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.