Welcome to WebmasterWorld Guest from 54.163.25.166

Forum Moderators: open

Message Too Old, No Replies

MySQL - Order By - Dates (multiple fields)

I need to order by 2 date columns, some may be blank

     
5:29 pm on Sep 1, 2010 (gmt 0)

New User

5+ Year Member

joined:Aug 14, 2010
posts: 8
votes: 0


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! :)
2:19 pm on Sept 3, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


Happy,

What you want to do is in the select portion of the statement you want to add is return the date twice once as it's true self then once as an aliased field.

Then you can add a statement that checks if it has a value, if no then make it a date way in the future.

Something like

SELECT
ODate,
CASE ODate
WHEN '' THEN '3010-12-19'
END as sort_date
ORDER BY sort_date

Of course you will have to make a note to update this code in a 1000 years. By then that date will no longer be a future date ;)
4:56 pm on Sept 3, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Get your head around how this works [webmasterworld.com]. Basically, while the sequence 1,5,6,7,0,0,0,0 would put all the 0's at the top, ordering by an expression allows the numbers to come to the top.

So a way that would probably work is

select * from sample_data order by SDate <> '0000-00-00' desc, ODate <> '0000-00-00' desc;

That would put all the SDates first, followed by the 0's of sdate, followed by the odates. I don't know that there's a way around it but that would set you on the road to finding it.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members