Search code examples
mysqlsql-order-by

MySQL: Ordering with boolean case and timestamp (and not boolean then timestamp)

I have a table with some columns :

Id Active timestamp
1 TRUE 23-09-10
2 NULL 23-09-11
3 FALSE 23-09-12
4 TRUE 23-09-15
5 NULL 23-09-16

I want to order my results according to this priority order If boolean is TRUE then order by boolean TRUE and timestamp, then other boolean values (false or null) and timestamp.

First time playing with order BY with CASE...

I tried :

ORDER BY (case when active then 0 else 1 end), timestamp;

or

ORDER BY actif IS TRUE DESC, timestamp;

It orders with Boolean true, then false, then Timestamp

Id Active timestamp
1 TRUE 23-09-10
4 TRUE 23-09-15
3 FALSE 23-09-12
2 NULL 23-09-11
5 NULL 23-09-16

What I want to achieve is "true and timestamp" then "FALSE|NULL and timestamp"

Id Active timestamp
1 TRUE 23-09-10
4 TRUE 23-09-15
2 NULL 23-09-11
3 FALSE 23-09-12
5 NULL 23-09-16
Solution

  • Looks like active column is a string. You can write:

    ORDER BY CASE WHEN active = 'TRUE' THEN 0 ELSE 1 END, timestamp