homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

MySQL - Order By - Dates (multiple fields)
I need to order by 2 date columns, some may be blank

Msg#: 4195142 posted 5:29 pm on Sep 1, 2010 (gmt 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).


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! :)



WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4195142 posted 2:19 pm on Sep 3, 2010 (gmt 0)


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

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 ;)


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

Msg#: 4195142 posted 4:56 pm on Sep 3, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved