homepage Welcome to WebmasterWorld Guest from 54.205.205.47
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4195144
 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).

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

 

Demaestro




msg:4196073
 2:19 pm on Sep 3, 2010 (gmt 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 ;)

rocknbil




msg:4196148
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved