Geek Answers Handbook

ORDER BY timestamp with NULL between future and past

I would like to order SQL results in the timestamp field in descending order with the newest records. However, I have certain lines that are empty or contain zeros. How can I merge this result between future and past rows? Can this be done with CASE?

SELECT * FROM table ORDER BY when DESC

EDIT: Thanks to all the answers. Just to let everyone know, I went with MySQL IFNULL, i.e.

SELECT * FROM table ORDER BY IFNULL(when,UNIX_TIMESTAMP()) DESC

This was the easiest approach, where if it contained NULL, the select query replaced it with the current unix time. Please note that I updated my DB and replaced all 0 with NULL values.

+5
source share
4 answers
SELECT * FROM TABLE IF(mytime is null, [sometime],mytime) ORDER BY ...

100% , "null" - , - .

+1

:

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

CASE:

SELECT *
FROM   mytable
ORDER  BY CASE WHEN mytime IS NULL OR mytime = 0 THEN now() ELSE mytime END DESC;

FALSE TRUE, DESC .
"Zero" MySQL .

+4
SELECT   *
         , COALESCE(when, '2011-01-01 00:00:00') as new_when
FROM     table
ORDER BY new_when DESC

COALESCE(). 2011-01-01 00:00:00 , , null . , new_when .

+2

, , , ...

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
+1

More articles:


All Articles