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.

Answers


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

Need Your Help

MySQL: Advisable number of rows

mysql performance database-design optimization

Consider an indexed MySQL table with 7 columns, being constantly queried and written to. What is the advisable number of rows that this table should be allowed to contain before the performance wou...

Two versions of my input function, recursion vs while-loop?

python recursion input while-loop

I need to have a function which returns a guaranteed input of type float.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.