Menu
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"?
838
Although it is most often used there, CASE is not limited to SELECT statements. For example, you can use it in clauses like IN , WHERE , HAVING , and ORDER BY . Using a CASE statement in a query once doesn't mean you have hit your quota for using it. You can use it multiple times in a single query.
If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.
SQL order by case can be used when we have to order the data on a conditional basis and define the criteria on which the ordering will be done based on a certain condition.
CASE Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition3 THEN result3 ELSE result END; ORDER BY: This keyword is used to sort the result-set in ascending or descending order. It sorts the records in ascending order by default.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
