Problem ordering by date

coffear

Hi All

I currently have a problem/ I am trying to pull information from a mySQL database. I wish to order it by date BUT the date can be null.

I wish to order by this date such as:-

SELECT expiry_date
FROM table_name
ORDER BY expiry_date ASC
LIMIT 5;

This of course works bar from 1 thing. I wish for the null values not to appear unless the limit cannot be populated with other entries (ie I want them at the back of the queue). The problem is by default null comes first when ordered this way. Is it possible so that the null values can be the last results or should I just use php to organize this (which is a last resort).

Just a note, this is an over simplified query but of course demonstrates my need)

Cheers in advance.

coffear

Just a note

I have managed to do it but possibly not the best way.

I have done the following:-

SELECT COALESCE(`expiry_date`,DATE_ADD(NOW(), INTERVAL 1 YEAR)) AS `mock_date`, `expiry_date` AS `expiry_date`
FROM table_name
ORDER BY `mock_date` ASC
LIMIT 5;

The dates returned should not be past a year in advance so the query should be fine.

Just in case people are not aware COALESCE() returns the first non null value, so it will return the normal expiry date if it is not null otherwise it will return the current date plus a year.

cgraz

Your logic is right, but you could do this much simpler using

select expiry_date
 from <table_name>
order 
   by coalesce(expiry_date, '20991231')
limit 5

coffear

Hi cgraz.

Not long after posting the solution I had thought of that but never got around to trying it. And it does look like a much better way of doing it, no redundant data returned.

Cheers

Sxooter

You could try to do it:

order by expiry_date is null, expiray_date ASC