Question 258946 in Mysql Question posted in
The official MySQL documentation can be found here.

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
Tags:

2

Answers


  1. 0

    If the ordering "All TRUE then all FALSE then all NULL" is safe for you then

    ORDER BY active = TRUE DESC, `timestamp`

    If you want "All TRUE then all FALSE/NULL mixed" then

    ORDER BY NULLIF(active, FALSE) DESC, `timestamp`
    Login or Signup to reply.
  2. 0

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

    ORDER BY CASE WHEN active = 'TRUE' THEN 0 ELSE 1 END, timestamp
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search