ORDER BY timestamp with NULLs between future and past
I would like to order SQL results by a timestamp field in descending order with newest entries first. However, I have certain rows that are blank or contain zeros. How can I sandwich these result in between future and past rows? Can this be done with CASEs?
SELECT * FROM table ORDER BY when DESC
EDIT: Thanks to all the responses. Just so everyone knows, I went with MySQL's IFNULL, i.e.
SELECT * FROM table ORDER BY IFNULL(when,UNIX_TIMESTAMP()) DESC
This was the simplest approach, where if when contained NULL the select query replaced it with the current unix time. Note that I updated my DB and replaced all 0s with NULL values.
SELECT * FROM TABLE IF(mytime is null, [sometime],mytime) ORDER BY ...
Not 100% sure whether 'is null' is the code, but I've done something similar.
The simplest version should be:
SELECT * FROM mytable ORDER BY (mytime > now() AND mytime IS NOT NULL) DESC -- future times first ,(mytime IS NULL OR mytime = 0) DESC -- NULL and "zero" next ,mytime DESC; -- everything descending
Or even simpler with a CASE statement:
SELECT * FROM mytable ORDER BY CASE WHEN mytime IS NULL OR mytime = 0 THEN now() ELSE mytime END DESC;
FALSE sorts before TRUE, therefore we need DESC to sort the hits first. Read about the special value "Zero" in MySQL in the manual.
SELECT * , COALESCE(when, '2011-01-01 00:00:00') as new_when FROM table ORDER BY new_when DESC
More about COALESCE(). Basically I'm using 2011-01-01 00:00:00 as the default timestamp if one is not set, so any items with null will be treated as that date. We alias that date as new_when and order by that.
I'm not sure if this syntax works but I think this is the idea...
ORDER BY CASE WHEN mytime IS NOT NULL AND mytime > NOW() THEN 'a' WHEN mytime IS NULL THEN 'b' WHEN mytime IS NOT NULL AND mytime < NOW() THEN 'c' END , mytime