...

View Full Version : Advanced ORDER BY question involving grouping



chrisrozwod
03-14-2013, 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
03-14-2013, 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 = '1900-1-1' THEN 2
WHEN DT = '1900-1-2' THEN 3
ELSE 4 END ) ASC,
IF( DT > CURDATE(), DT, '2099-1-1' ) ASC,
IF( DT = '1900-1-1', ID, 0 ) DESC,
IF( DT = '1900-1-2', ID, 0 ) ASC,
IF( DT > '1900-1-2' AND DT <= CURDATE(), DT, '1900-1-1') DESC

100% *UNTESTED* but it feels right to me.

Old Pedant
03-14-2013, 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 | 2012-01-01 |
| 2 | 2012-02-02 |
| 3 | 2012-03-03 |
| 4 | 1900-01-01 |
| 5 | 1900-01-01 |
| 6 | 2014-01-01 |
| 7 | 1900-01-02 |
| 8 | 2014-02-02 |
| 9 | 2014-03-03 |
| 10 | 1900-01-02 |
+------+------------+

And then I used this query:


mysql> select * from zak
-> ORDER BY ( CASE WHEN DT > CURDATE() THEN 1
-> WHEN DT = '1900-1-1' THEN 2
-> WHEN DT = '1900-1-2' THEN 3
-> ELSE 4 END ) ASC,
-> IF( DT > CURDATE(), DT, '2099-1-1' ) ASC,
-> IF( DT = '1900-1-1', ID, 0 ) DESC,
-> IF( DT = '1900-1-2', ID, 0 ) ASC,
-> IF( DT > '1900-1-2' AND DT <= CURDATE(), DT, '1900-1-1') DESC;

and got these results--which I have annotated after the dt column:


+------+------------+
| id | dt |
+------+------------+
| 6 | 2014-01-01 | group 1
| 8 | 2014-02-02 | group 1
| 9 | 2014-03-03 | group 1 -- future date, DT in ASCending order
| 5 | 1900-01-01 | group 2
| 4 | 1900-01-01 | group 2 -- "ALL" flag value, ID in DESCending order
| 7 | 1900-01-02 | group 3
| 10 | 1900-01-02 | group 3 -- "TBC" flag value, ID in ASCending order
| 3 | 2012-03-03 | group 4
| 2 | 2012-02-02 | group 4
| 1 | 2012-01-01 | group 4 -- past dates (not flags) in DESCending order
+------+------------+

Old Pedant
03-14-2013, 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 = '1900-1-1' THEN 2
-> WHEN DT = '1900-1-2' THEN 3
-> ELSE 4 END ) AS groupNumber, id, DT
-> FROM zak
-> ORDER BY groupNumber ASC,
-> IF( groupNumber=1, DT, '2099-1-1' ) ASC,
-> IF( groupNumber=2, ID, 0 ) DESC,
-> IF( groupNumber=3, ID, 0 ) ASC,
-> IF( groupNumber=4, DT, '1900-1-1') DESC;
+-------------+------+------------+
| groupNumber | id | DT |
+-------------+------+------------+
| 1 | 6 | 2014-01-01 |
| 1 | 8 | 2014-02-02 |
| 1 | 9 | 2014-03-03 |
| 2 | 5 | 1900-01-01 |
| 2 | 4 | 1900-01-01 |
| 3 | 7 | 1900-01-02 |
| 3 | 10 | 1900-01-02 |
| 4 | 3 | 2012-03-03 |
| 4 | 2 | 2012-02-02 |
| 4 | 1 | 2012-01-01 |
+-------------+------+------------+
10 rows in set (0.00 sec)

chrisrozwod
03-14-2013, 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
03-14-2013, 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 = '1900-1-1' THEN 2
WHEN DT = '1900-1-2' THEN 3
ELSE 4 END ) AS groupNumber, id, DT
FROM zak
ORDER BY groupNumber ASC,
IF( groupNumber=1, DT, '2099-1-1' ) ASC,
IF( groupNumber IN (2,3), ID, 0 ) DESC,
IF( groupNumber=4, DT, '1900-1-1') DESC;

chrisrozwod
03-14-2013, 04:22 AM
Unsurprisingly, that also worked flawlessly. Thanks. That groupNumber is really helpful.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum