Professional Web Applications Themes

Order By and Group By - MySQL

hi all, I have a table below... c1 c2 c3 1 1 a 1 2 b 2 1 c 2 2 d 3 1 e 3 2 f I want to get (1,2,b), (2,2,d), (3,2,f), i.e. group by c1, which having the largest c2, and select the row but the following quesy can't work... SELECT * FROM `table_1` GROUP BY c1 ORDER BY c2 how to do this? thanks....

  1. #1

    Default Order By and Group By

    hi all,

    I have a table below...

    c1 c2 c3
    1 1 a
    1 2 b
    2 1 c
    2 2 d
    3 1 e
    3 2 f


    I want to get (1,2,b), (2,2,d), (3,2,f), i.e. group by c1, which having
    the largest c2, and select the row

    but the following quesy can't work...

    SELECT *
    FROM `table_1`
    GROUP BY c1
    ORDER BY c2

    how to do this? thanks.

    howa Guest

  2. #2

    Default Re: Order By and Group By

    > I have a table below... 
    [...]

    select * from t
    group by c1,c2
    having c2=(select max(c2) from t)
    order by c1;



    Regards
    Dimitre


    Radoulov, Guest

  3. #3

    Default Re: Order By and Group By


    howa wrote: 

    Sheesh, this old chestnut comes up sooooo often! There are so many
    recent threads about this question, which Strawberry and I have
    answered, I'm amazed that no one can find the answer by searching the
    newsgroup.
    Oh well, here we go with the answer again...

    SELECT t1. *
    FROM `table_1` t1
    LEFT JOIN `table_1` t2 ON t1.c1 = t2.c1 AND t1.c2 < t2.c2
    WHERE t2.c1 IS NULL

    Dimitre's one will only work on recent versions of MySQL but is nowhere
    near as efficienct as the above method.

    Captain Guest

  4. #4

    Default Re: Order By and Group By


    howa wrote: 

    SELECT DISTINCT (t1.c1), t2.c2, t2.c3
    FROM mytable t1
    LEFT JOIN mytable t2 ON t2.c1 = t1.c1
    AND t2.c2 > t1.c2
    HAVING !ISNULL(t2.c2);

    strawberry Guest

  5. #5

    Default Re: Order By and Group By

     
    >
    > Sheesh, this old chestnut comes up sooooo often! There are so many
    > recent threads about this question, which Strawberry and I have
    > answered, I'm amazed that no one can find the answer by searching the
    > newsgroup.
    > Oh well, here we go with the answer again...
    >
    > SELECT t1. *
    > FROM `table_1` t1
    > LEFT JOIN `table_1` t2 ON t1.c1 = t2.c1 AND t1.c2 < t2.c2
    > WHERE t2.c1 IS NULL
    >
    > Dimitre's one will only work on recent versions of MySQL but is nowhere
    > near as efficienct as the above method.[/ref]

    May be I didn't understand what the OP wanted.
    See result/elapsed.

    If your data is something like this:


    CREATE TABLE `t3` (
    `c1` int(11) default NULL,
    `c2` int(11) default NULL,
    `c3` varchar(10) default NULL,
    KEY `t3_i` (`c1`,`c2`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1

    mysql> select * from t3 limit 20;
    +------+------+------+
    | c1 | c2 | c3 |
    +------+------+------+
    | 1 | 1 | a |
    | 1 | 2 | b |
    | 2 | 1 | c |
    | 2 | 2 | d |
    | 3 | 1 | e |
    | 3 | 2 | f |
    | 4 | 1 | g |
    | 4 | 2 | h |
    | 5 | 1 | i |
    | 5 | 2 | j |
    | 6 | 1 | k |
    | 6 | 2 | l |
    | 7 | 1 | m |
    | 7 | 2 | n |
    | 8 | 1 | o |
    | 8 | 2 | p |
    | 1 | 1 | a |
    | 1 | 2 | b |
    | 2 | 1 | c |
    | 2 | 2 | d |
    +------+------+------+
    20 rows in set (0.00 sec)


    mysql> select count(1) from t3;
    +----------+
    | count(1) |
    +----------+
    | 1600 |
    +----------+
    1 row in set (0.00 sec)


    SELECT t1. *
    FROM `t3` t1
    LEFT JOIN `t3` t2 ON t1.c1 = t2.c1 AND t1.c2 < t2.c2
    WHERE t2.c1 IS NULL;

    [snip]

    | 4 | 2 | h |
    | 5 | 2 | j |
    | 6 | 2 | l |
    | 7 | 2 | n |
    | 8 | 2 | p |
    | 1 | 2 | b |
    | 2 | 2 | d |
    +------+------+------+
    800 rows in set (0.52 sec)

    mysql> select * from t3
    -> group by c1,c2
    -> having c2=(select max(c2) from t3)
    -> order by c1;
    +------+------+------+
    | c1 | c2 | c3 |
    +------+------+------+
    | 1 | 2 | b |
    | 2 | 2 | d |
    | 3 | 2 | f |
    | 4 | 2 | h |
    | 5 | 2 | j |
    | 6 | 2 | l |
    | 7 | 2 | n |
    | 8 | 2 | p |
    +------+------+------+
    8 rows in set (0.01 sec)


    Regards
    Dimitre





    Radoulov, Guest

  6. #6

    Default Re: Order By and Group By


    Radoulov, Dimitre wrote: 
    > >
    > > Sheesh, this old chestnut comes up sooooo often! There are so many
    > > recent threads about this question, which Strawberry and I have
    > > answered, I'm amazed that no one can find the answer by searching the
    > > newsgroup.
    > > Oh well, here we go with the answer again...
    > >
    > > SELECT t1. *
    > > FROM `table_1` t1
    > > LEFT JOIN `table_1` t2 ON t1.c1 = t2.c1 AND t1.c2 < t2.c2
    > > WHERE t2.c1 IS NULL
    > >
    > > Dimitre's one will only work on recent versions of MySQL but is nowhere
    > > near as efficienct as the above method.[/ref]
    >
    > May be I didn't understand what the OP wanted.
    > See result/elapsed.
    >
    > If your data is something like this:
    >
    >
    > CREATE TABLE `t3` (
    > `c1` int(11) default NULL,
    > `c2` int(11) default NULL,
    > `c3` varchar(10) default NULL,
    > KEY `t3_i` (`c1`,`c2`)
    > ) ENGINE=MyISAM DEFAULT CHT=latin1
    >
    > mysql> select * from t3 limit 20;
    > +------+------+------+
    > | c1 | c2 | c3 |
    > +------+------+------+
    > | 1 | 1 | a |
    > | 1 | 2 | b |
    > | 2 | 1 | c |
    > | 2 | 2 | d |
    > | 3 | 1 | e |
    > | 3 | 2 | f |
    > | 4 | 1 | g |
    > | 4 | 2 | h |
    > | 5 | 1 | i |
    > | 5 | 2 | j |
    > | 6 | 1 | k |
    > | 6 | 2 | l |
    > | 7 | 1 | m |
    > | 7 | 2 | n |
    > | 8 | 1 | o |
    > | 8 | 2 | p |
    > | 1 | 1 | a |
    > | 1 | 2 | b |
    > | 2 | 1 | c |
    > | 2 | 2 | d |
    > +------+------+------+
    > 20 rows in set (0.00 sec)
    >
    >
    > mysql> select count(1) from t3;
    > +----------+
    > | count(1) |
    > +----------+
    > | 1600 |
    > +----------+
    > 1 row in set (0.00 sec)
    >
    >
    > SELECT t1. *
    > FROM `t3` t1
    > LEFT JOIN `t3` t2 ON t1.c1 = t2.c1 AND t1.c2 < t2.c2
    > WHERE t2.c1 IS NULL;
    >
    > [snip]
    >
    > | 4 | 2 | h |
    > | 5 | 2 | j |
    > | 6 | 2 | l |
    > | 7 | 2 | n |
    > | 8 | 2 | p |
    > | 1 | 2 | b |
    > | 2 | 2 | d |
    > +------+------+------+
    > 800 rows in set (0.52 sec)
    >
    > mysql> select * from t3
    > -> group by c1,c2
    > -> having c2=(select max(c2) from t3)
    > -> order by c1;
    > +------+------+------+
    > | c1 | c2 | c3 |
    > +------+------+------+
    > | 1 | 2 | b |
    > | 2 | 2 | d |
    > | 3 | 2 | f |
    > | 4 | 2 | h |
    > | 5 | 2 | j |
    > | 6 | 2 | l |
    > | 7 | 2 | n |
    > | 8 | 2 | p |
    > +------+------+------+
    > 8 rows in set (0.01 sec)
    >
    >
    > Regards
    > Dimitre[/ref]

    His statement of requirements was "I want to get (1,2,b), (2,2,d),
    (3,2,f), i.e. group by c1, which having the largest c2, and select the
    row", which was of course totally different from his sample query which
    showed a GROUP BY clause with c1 and c2.

    Captain Guest

  7. #7

    Default Re: Order By and Group By

    hi there,

    I was having a very similar problem, where the proposed solutions do not
    work (or I didn't try hard enough)...

    this is a part of my data:

    SELECT orb, mn, ps, q, t
    FROM pixelMask
    WHERE orb BETWEEN 23016 AND 23019
    ORDER BY orb ASC, q DESC, ps DESC, t DESC

    [23016, 602, 'P', 100, datetime.datetime(2006, 9, 15, 11, 5, 7)]
    [23016, 205, 'N', 100, datetime.datetime(2006, 8, 14, 4, 50, 21)]
    [23017, 603, 'P', 100, datetime.datetime(2006, 9, 26, 3, 33, 33)]
    [23017, 206, 'N', 100, datetime.datetime(2006, 8, 14, 4, 51, 52)]
    [23018, 604, 'P', 100, datetime.datetime(2006, 9, 26, 3, 34, 52)]
    [23018, 207, 'N', 100, datetime.datetime(2006, 8, 14, 4, 53, 25)]
    [23019, 765, 'P', 50, datetime.datetime(2006, 9, 26, 3, 36, 22)]
    [23019, 693, 'P', 50, datetime.datetime(2006, 9, 26, 3, 35, 53)]
    [23019, 605, 'P', 50, datetime.datetime(2006, 9, 26, 3, 35, 23)]
    [23019, 208, 'N', 50, datetime.datetime(2006, 8, 14, 4, 54, 12)]

    I have ordered the data already, first by orb and then by descending
    "score", I want just the first row for each distinct orb.

    with PostgreSQL I would do this:

    SELECT DISTINCT ON (orb) orb, mn, ps, q, t
    FROM pixelMask
    WHERE orb BETWEEN 23016 AND 23019
    ORDER BY orb ASC, q DESC, ps DESC, t DESC

    and get the result wanted

    [23016, 602, 'P', 100, datetime.datetime(2006, 9, 15, 11, 5, 7)]
    [23017, 603, 'P', 100, datetime.datetime(2006, 9, 26, 3, 33, 33)]
    [23018, 604, 'P', 100, datetime.datetime(2006, 9, 26, 3, 34, 52)]
    [23019, 765, 'P', 50, datetime.datetime(2006, 9, 26, 3, 36, 22)]

    I'm not so sure whether DISTINCT ON (<field_list>) is part of the standard
    or not, but MySQL does not support it anyways...

    MySQL does implement a non standard extension to 'GROUP BY': """MySQL
    extends the use of GROUP BY to allow selecting fields that are not mentioned
    in the GROUP BY clause"""

    given this extension, if GROUP BY could follow ORDER BY, I would do this:

    SELECT orb, mn, ps, q, t
    FROM pixelMask
    WHERE orb BETWEEN 23016 AND 23019
    ORDER BY orb ASC, q DESC, ps DESC, t DESC
    GROUP BY orb

    unfortunately, GROUP BY must precede ORDER BY, so I need a subquery...

    SELECT * FROM (
    SELECT orb, mn, ps, q, t
    FROM pixelMask
    WHERE orb BETWEEN 23016 AND 23019
    ORDER BY orb ASC, q DESC, ps DESC, t DESC) result
    GROUP BY orb ORDER BY NULL

    ....

    I'm adding that 'ORDER BY NULL' just to be sure that the GROUP BY does not
    waste any time ordering ordered data...

    I assume that this is faster than something containing a JOIN...

    regards,
    HTH,

    Mario
    Mario Guest

  8. #8

    Default Re: Order By and Group By

    On 9 Nov 2006 01:47:47 -0800, Captain Paralytic wrote: 
    >
    > Sheesh, this old chestnut comes up sooooo often! There are so many
    > recent threads about this question, which Strawberry and I have
    > answered, I'm amazed that no one can find the answer by searching the
    > newsgroup.[/ref]

    This is of a sort that I classify as a "homework question". Frequently
    it can be solved by doing a little homework. Sometimes, the question IS
    homework. Routine questions that are heavily abstracted are doubly
    suspicious.

    --
    60. My five-year-old child advisor will also be asked to decipher any code I
    am thinking of using. If he breaks the code in under 30 seconds, it will
    not be used. Note: this also applies to passwords.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

Similar Threads

  1. order the GROUP BY visual
    By salvatore in forum MySQL
    Replies: 3
    Last Post: October 24th, 01:01 AM
  2. group by and order
    By j0sh in forum MySQL
    Replies: 0
    Last Post: September 13th, 11:01 AM
  3. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  4. group by and order by
    By steeban in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: May 25th, 06:56 AM
  5. MYSQL query using GROUP BY and ORDER BY?
    By Bonge Boo! in forum PHP Development
    Replies: 3
    Last Post: August 15th, 06:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139