CODEWITHSUNDEEP
mysqlsql
user3608238
user3608238

Reputation: 425

MySQL Order by Date, with NULL first

I have a select statement that I would like to select 1 record from a table. The structure is:

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

I would like to select the item with a NULL start time, but if that does not exist I would like it to select the latest start_time. I have tried using ORDER with LIMIT 1, but using ORDER BY start_time either gives NULL first followed by the earliest starting, or latest starting then NULL. Is it possible to have result order 1,3,2 ?

Upvotes: 3

Views: 1404

Answers (2)

AAA
AAA

Reputation: 1384

You can have two different ORDER BY expressions:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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

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

Upvotes: 6

Related Questions