Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Sep 2010
    Location
    Beaverton, OR
    Posts
    38
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Advanced ORDER BY question involving grouping

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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...
    Code:
    ....
    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.
    Last edited by Old Pedant; 03-14-2013 at 03:47 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    chrisrozwod (03-14-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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:
    Code:
    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:
    Code:
    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:
    Code:
    +------+------------+
    | 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
    +------+------------+
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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:
    Code:
    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)
    Last edited by Old Pedant; 03-14-2013 at 04:00 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Sep 2010
    Location
    Beaverton, OR
    Posts
    38
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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.
    Last edited by chrisrozwod; 03-14-2013 at 04:08 AM. Reason: Just saw the new posts

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    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:
    Code:
    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;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Sep 2010
    Location
    Beaverton, OR
    Posts
    38
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Unsurprisingly, that also worked flawlessly. Thanks. That groupNumber is really helpful.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •