Page 1 of 1

MySQL - Order By - Dates (multiple fields)

Posted: Wed Sep 01, 2010 12:31 pm
by HappyJupiter
I have a table that contains (among other things) 2 date fields.

Either one or the other field must be filled out (or both).

Fields:
---------
SDate
ODate

Sample data:
------------
#1 SDate: 2010-02-01
#1 ODate: 2010-02-14

#2 SDate: 0000-00-00
#2 ODate: 2010-02-19

#3 SDate: 2010-02-23
#3 ODate: 0000-00-00

If I do an ORDER BY SDate DESC, ODate DESC it would return:
#3, #1, #2
This is because #2 ODate has not been entered.

But what I would like is:
#3, #2, #1

Basically I'd like to return the records in descending order (based on either field) regardless of whether or not both dates have been supplied.

Hopefully this makes sense...

Thanks in advance! Any help would be greatly appreciated! :)

Re: MySQL - Order By - Dates (multiple fields)

Posted: Wed Sep 01, 2010 1:11 pm
by mikosiko
only if the "null" value for your date fields is "0000-00-00" and not the NULL value this could be an alternative

Code: Select all

SELECT * from TABLE
ORDER BY GREATEST(SDate,ODate) DESC

Re: MySQL - Order By - Dates (multiple fields)

Posted: Wed Sep 01, 2010 5:28 pm
by HappyJupiter
Fabulous! This is just what I needed, works great! Thanks for the help :)