[SOLVED] custom sorting order of dates in MySQL

[svivian]

I have some data I wish to sort by date (ascending), however, some of the dates are missing and thus have the default '0000-00-00' as their entry.

 

When sorting in ascending order these appear at the top of the list, is there a way to put them at the bottom of the list but still have the rest in ascending order? (I know I can do two queries but I'd like one if possible.)

[svivian]

Ah, found a solution:

ORDER BY strcmp(date,'0000-00-00') DESC, date ASC

 

This works since strcmp(date,'0000-00-00') is 0 if the date is '0000-00-00' and 1 if a "higher string" (later alphabetically). So everything with a real date comes before that without from the first ordering, then the second ordering takes care of the entries with a date.

[svivian]

OK this was working on localhost but it's not working now I've uploaded to the internet, My host has MySQL client version 4.1.22. According to the MySQL manual, the STRCMP function existed in this version.

 

I just ran a query with

SELECT id, name, date, strcmp( date, '0000-00-00' ) ...

and this returns 1 for everything, whether a real date or '0000-00-00'.

 

Any idea why this might be?!

[toplay]

Are you sure your column is of type DATE and not of DATETIME?

 

Either of these ways would work (the date column in these is assumed a DATETIME type):

 

select STRCMP(DATE(date_column), '0000-00-00') AS sort_order, date_column from table_name

order by sort_order DESC, date_column ASC

;

 

select IF(DATE(date_column) = '0000-00-00', 0, 1) AS sort_order, date_column from table_name

order by sort_order DESC, date_column ASC

;

 

[Barand]

... ORDER BY date = '0000-00-00' , date

 

(or you might need ... ORDER BY date = '0000-00-00'  DESC, date)

[toplay]

Yes, Barand's approach is a shortcut. It may not be as clear to a third person trying to look at the query and decipher it.

 

If it's a DATETIME column you'll still need this:

 

... order by date(date_column) = '0000-00-00' ASC, date_column ASC

 

or this:

 

... order by date_column = '0000-00-00 00:00:00' ASC, date_column ASC

 

 

[svivian]

Thanks, the simple = thing worked. The field is definitely a date field, still don't know why it doesn't work on the earlier version of MySQL but is works now, that's all that matters.

 

Final 'order by' clause:

ORDER BY date='0000-00-00' ASC, date ASC