stevendesu stevendesu - 28 days ago 8
MySQL Question

Conditionally change sort direction in SQL

I have an interesting take on a similar question that was asked many years ago on StackOverflow

For simplicity, suppose I have a table with three columns:

+----+------+-------+
| id | cond | value |
+----+------+-------+
| 1 | 1 | "A" |
| 2 | 0 | "B" |
| 3 | 1 | "C" |
| 4 | 0 | "D" |
| 5 | 1 | "E" |
+----+------+-------+


Now I want to sort this table first by
cond
, then by
value
ascending if
cond
is 0 and descending if
cond
is 1. The final sorted table should look like:

+----+------+-------+
| id | cond | value |
+----+------+-------+
| 2 | 0 | "B" |
| 4 | 0 | "D" |
| 5 | 1 | "E" |
| 3 | 1 | "C" |
| 1 | 1 | "A" |
+----+------+-------+


Note that I can't depend on
value
to be numerical, so I can't do something clever like:

order by cond, (case when cond = 0 then value else -value end)

Answer Source

Use two separated conditional cond to order your result:

select *
from yourtable
order by 
    cond, 
    case when cond = 0 then `value` else 1 end,
    case when cond = 1 then `value` else 1 end desc

See SQLFiddle DEMO here.