ORDER BY timestamp with NULLs between future and past

Question

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.

Answer 1

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.