|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)|
I have a table that contains (among other things) 2 date fields.
Either one or the other field must be filled out (or both).
#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 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.
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 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.