SQL for sorting boolean column as true, null, false
47,834
Solution 1
Not beautiful, but should work:
... order by (case when f1 then 1 when f1 is null then 2 else 3 end) asc
Solution 2
A better solution would be to use
f1 DESC NULLS LAST
if you're okay with the order true, false, nil ( I guess the important part of your question was, like in my situation now, to have the not-true vaules together)
https://stackoverflow.com/a/7621232/1627888
Solution 3
you could do also as follows:
order by coalesce(f1, FALSE), coalesce(f1, TRUE), ...
If f1
is TRUE
, you get: TRUE, TRUE
If f1
is NULL
, you get: FALSE, TRUE
If f1
is FALSE
, you get: FALSE, FALSE
which corresponds to the sorting order you want.
Author by
petehern
Updated on January 20, 2022Comments
-
petehern over 2 years
My table has three boolean fields: f1, f2, f3. If I do
SELECT * FROM table ORDER BY f1, f2, f3
the records will be sorted by these fields in the order false, true, null. I wish to order them with null in between true and false: the correct order should be true, null, false.
I am using PostgreSQL.