MySQL - Order By - Dates (multiple fields)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
HappyJupiter
Forum Newbie
Posts: 6
Joined: Sat Aug 14, 2010 10:36 am

MySQL - Order By - Dates (multiple fields)

Post 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! :)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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
HappyJupiter
Forum Newbie
Posts: 6
Joined: Sat Aug 14, 2010 10:36 am

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

Post by HappyJupiter »

Fabulous! This is just what I needed, works great! Thanks for the help :)
Post Reply