View Full Version : Advanced ORDER BY question involving grouping
chrisrozwod 03142013, 02:53 AM Hi, so I've been trying to figure out this question for a few days. I would really appreciate help.
I have a table that needs to be sorted by date. However, within those dates there are four groups (we'll call them dategroups), and they need to be in a certain order too.
dategroup1 needs to have all upcoming dates. It should be ordered by date ASC
dategroup2 is going to be a specific date I use to represent "All Dates". This should be ordered by id DESC.
dategroup3 is going to be a specific date I use to represent "TBC". This should also be ordered by id DESC.
dategroup4 needs to have all previous dates. It should be ordered by date DESC.
I don't know how to create groups like that. It's like I need multiple queries, or something. I need to create those groups from only the date column, and then display the dategroups in the correct order.
Old Pedant 03142013, 03:40 AM It would have helped if you'd shown the query as it is now and what "specific date" you are using to represent "All Dates" and "TBC".
So I'm going to use Jan 1, 1900 to mean "All Dates" and Jan 2, 1900 to mean "TBC". And I'm going to *ASSUME* that you have a "DT" field that contains DATE values (dates, with not time parts, though it probably works even with time parts).
So...
....
ORDER BY ( CASE WHEN DT > CURDATE() THEN 1
WHEN DT = '190011' THEN 2
WHEN DT = '190012' THEN 3
ELSE 4 END ) ASC,
IF( DT > CURDATE(), DT, '209911' ) ASC,
IF( DT = '190011', ID, 0 ) DESC,
IF( DT = '190012', ID, 0 ) ASC,
IF( DT > '190012' AND DT <= CURDATE(), DT, '190011') DESC
100% *UNTESTED* but it feels right to me.
Old Pedant 03142013, 03:52 AM Decided I had to try it. Good think. I had a couple of typos. But the logic was correct.
I created a table "zak" with this data in it:
mysql> select * from zak order by id;
+++
 id  dt 
+++
 1  20120101 
 2  20120202 
 3  20120303 
 4  19000101 
 5  19000101 
 6  20140101 
 7  19000102 
 8  20140202 
 9  20140303 
 10  19000102 
+++
And then I used this query:
mysql> select * from zak
> ORDER BY ( CASE WHEN DT > CURDATE() THEN 1
> WHEN DT = '190011' THEN 2
> WHEN DT = '190012' THEN 3
> ELSE 4 END ) ASC,
> IF( DT > CURDATE(), DT, '209911' ) ASC,
> IF( DT = '190011', ID, 0 ) DESC,
> IF( DT = '190012', ID, 0 ) ASC,
> IF( DT > '190012' AND DT <= CURDATE(), DT, '190011') DESC;
and got these resultswhich I have annotated after the dt column:
+++
 id  dt 
+++
 6  20140101  group 1
 8  20140202  group 1
 9  20140303  group 1  future date, DT in ASCending order
 5  19000101  group 2
 4  19000101  group 2  "ALL" flag value, ID in DESCending order
 7  19000102  group 3
 10  19000102  group 3  "TBC" flag value, ID in ASCending order
 3  20120303  group 4
 2  20120202  group 4
 1  20120101  group 4  past dates (not flags) in DESCending order
+++
Old Pedant 03142013, 03:57 AM By the by, if you would like the group number to be output as part of your SELECT, it's a fairly easy reordering to do. Just move the CASE WHEN stuff into the SELECT. It also has the advantage of then making the ORDER BY more readable.
Thus:
mysql> SELECT ( CASE WHEN DT > CURDATE() THEN 1
> WHEN DT = '190011' THEN 2
> WHEN DT = '190012' THEN 3
> ELSE 4 END ) AS groupNumber, id, DT
> FROM zak
> ORDER BY groupNumber ASC,
> IF( groupNumber=1, DT, '209911' ) ASC,
> IF( groupNumber=2, ID, 0 ) DESC,
> IF( groupNumber=3, ID, 0 ) ASC,
> IF( groupNumber=4, DT, '190011') DESC;
++++
 groupNumber  id  DT 
++++
 1  6  20140101 
 1  8  20140202 
 1  9  20140303 
 2  5  19000101 
 2  4  19000101 
 3  7  19000102 
 3  10  19000102 
 4  3  20120303 
 4  2  20120202 
 4  1  20120101 
++++
10 rows in set (0.00 sec)
chrisrozwod 03142013, 04:01 AM Dude, you are the king! That worked perfectly.
The first option worked for me. I will look into the others as well. The groupNumber being output seems like it could be a worthwhile route to pursue for me. Again, thanks so much for taking the time. Can't even tell you how much this helped.
Old Pedant 03142013, 04:07 AM Whoops...I misread your requirements.
dategroup3 is going to be a specific date I use to represent "TBC". This should also be ordered by id DESC.
I made group 3 have ID ASC. Trivial to change, of course.
Actually, that means the GROUP BY can be a little simplified, especially if you output the groupNumber as part of the SELECT.
Thus:
SELECT ( CASE WHEN DT > CURDATE() THEN 1
WHEN DT = '190011' THEN 2
WHEN DT = '190012' THEN 3
ELSE 4 END ) AS groupNumber, id, DT
FROM zak
ORDER BY groupNumber ASC,
IF( groupNumber=1, DT, '209911' ) ASC,
IF( groupNumber IN (2,3), ID, 0 ) DESC,
IF( groupNumber=4, DT, '190011') DESC;
chrisrozwod 03142013, 04:22 AM Unsurprisingly, that also worked flawlessly. Thanks. That groupNumber is really helpful.

