Clever Geek Handbook

MySQL Order by Date, first NULL

I have a select statement that I would like to select 1 record from a table. Structure:

id | start_time 
--------------
1    NULL
2    2014-08-23
3    2014-09-01

I would like to select an item with a NULL start time, but if that doesn't exist, I would like it to select the last one start_time

. I've tried using ORDER

with LIMIT 1

, but using ORDER BY start_time

either prints NULL first and then earliest start, or last start then NULL. Is it possible to have the order of the result 1,3,2

?

+3


source to share


2 answers


You can use two sort expressions to get the order you want:



select t.*
from table t
order by (start_time is null) desc,
         start_time desc
limit 1;
+6


source


You can have two different expressions ORDER BY

:



SELECT * from table ORDER BY (start_time IS NULL) DESC, start_time DESC;

0


source






More articles:


All Articles