[SQL] order by boolean column as true, null, false

SQL for sorting boolean column as true, null, false

 

=> 1번 방법으로 sorting

@Query("SELECT * FROM $MISSING_TABLE_NAME ORDER BY (case when emergency then 1 else 2 end) asc")
suspend fun getAll(): List<MissingPersonLocalData>

---

 

Questions : SQL for sorting boolean column as true, null, false

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.

SQLPOSTGRESQLSORTINGBOOLEAN

Total Answers 6

Answers 1 : of SQL for sorting boolean column as true, null, false

Not beautiful, but should work:

   ... order by (case when f1 then 1 when f1 is null then 2 else 3 end) asc

Answers 2 : of SQL for sorting boolean column as true, null, false

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

Answers 3 : of SQL for sorting boolean column as true, null, false

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.

Answers 4 : of SQL for sorting boolean column as true, null, false

This should work better

order by (f1 is true) desc

Answers 5 : of SQL for sorting boolean column as true, null, false

You can sort any finite set of things in any order by using a case statement to map the elements to the counting numbers ...

Answers 6 : of SQL for sorting boolean column as true, null, false

It is also possible to do as follows:

... ORDER BY (CASE WHEN f1 IS NOT NULL THEN f1::int * 2 ELSE 1 END) DESC