# 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. ## 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. ## 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

3. ## Re: Order By and Group By

howa wrote:

Sheesh, this old chestnut comes up sooooo often! There are so many
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. ## 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. ## Re: Order By and Group By

>
> Sheesh, this old chestnut comes up sooooo often! There are so many
> 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

6. ## Re: Order By and Group By

> >
> > Sheesh, this old chestnut comes up sooooo often! There are so many
> > 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. ## 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
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
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
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
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. ## 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
> 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

#### Posting Permissions

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