7

I have a situation where I need to sort my records by their "status" which is made up of a combination of fields. Here is an example how it should return the results sorted by status in ascending order:

        |     Sent      Received         Approved
--------------------------------------------------
record1 |     null        null             null
record2 |  2012-01-01     null             null
record3 |  2012-01-01   2012-01-01         null
record4 |  2012-01-01   2012-01-01      2012-01-01

How would I create a MySQL query that would order these records by their overall "status"?

Andrew
  • 227,796
  • 193
  • 515
  • 708
  • "status" is the combination of sent, received, and approved. So records where sent is null should be first, etc...like the example above. – Andrew Feb 27 '12 at 17:35

1 Answers1

12
order by
   case when sent is null and received is null and approved is null then 1
        when received is null and approved is null then 2
        when approved is null then 3
        else 4 end
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • 1
    I'm getting an error whenever I try to order by a case statement: `Column not found: 1054 Unknown column 'CASE WHEN ... END' in 'order clause'` – Andrew Feb 27 '12 at 17:30
  • @Andrew, Then I would add the "case" to your select statement as a column name, give it a column name, such as " as MyOrderCol" and have your order by clause refer to that ... such as "order by MyOrderCol". If you have an error with the CASE WHEN, its probably because the example names you've provided are not the actual columns in the source table, but aliased result column names – DRapp Feb 27 '12 at 17:56
  • moving the case statement into the select statement seemed to fix the problem. – Andrew Feb 27 '12 at 18:01
  • I think I figured out why it wasn't working. My case statement was being wrapped in backticks, so MySQL was interpreting the whole thing as a field name. – Andrew Feb 27 '12 at 18:53