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