All geek questions in one place

MySQL: ORDER BY with an empty date '0000-00-00' as last but the rest ASC

In our database, instead of creating empty NULL dates, they are '0000-00-00' (I know this sucks). How can I arrange them so that dates that are not '0000-00-00' are first ordered by ASC and then empty dates '0000-00-00' appear after?

Thanks!

+4
source share
4 answers
 ... ORDER BY CASE WHEN YourDateColumn = '0000-00-00' THEN 2 ELSE 1 END, YourDateColumn
+11
source

Try below:

  SELECT * FROM your_table ORDER BY (date_column='0000-00-00'), date_column ASC

OR

 select * from your_table order by if(date_column='0000-00-00',1,0),date_column;
+3
source

You can use CASE WHEN http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

 ... ORDER BY (CASE WHEN date = '0000-00-00' THEN 1 ELSE 0 END) ASC, otherColumns asc, ...
+1
source 
 ORDER BY (DateColumn = 0) , DateColumn
+1
source

Source: https://habr.com/ru/post/1396931/

More articles:


All Articles