Home > database >  In MySQL, when ordering by more than one conditions, how do I treat "false" the same way a
In MySQL, when ordering by more than one conditions, how do I treat "false" the same way a

Time:05-20

I have a select statement where I want to order by a boolean column first, then order by a date column, and the goal is to put records with boolean = true at the top, then for the records with boolean = false OR boolean is NULL, they should be sorted by the date column`.

The statement is like

select *
from some_table
order by some_table.boolean_column desc, some_table.date_column desc

However, the issue is when a record's boolean_column is false (or 0), it is not sorted by date_column with those whose boolean_column is NULL, because 0 is consider larger than null, so this record is places ahead of others whose boolean_column is NULL, though its date_column is not the most recent.

I want to treat boolean_column = 0 the same way as boolean_column is null.

Could anyone teach me how to do that?

CodePudding user response:

Just use an expression:

order by coalesce(some_table.boolean_column,0) desc, ...
  • Related