SEO

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.

> 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
[...]

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



Regards
Dimitre

howa wrote:
> 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.

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.

howa wrote:
> 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
>

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);

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

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 CHARSET=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, Dimitre wrote:
> >> 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.
> >
> > 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.
>
> 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 CHARSET=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

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.

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

On 9 Nov 2006 01:47:47 -0800, Captain Paralytic wrote:
>
> howa wrote:
>> 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.
>
> 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.

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