一个示例表test(select * from test):
id gid age username
1 1 11 zhangsan
2 1 13 zhangsan
3 1 15 lisi
4 1 10 wangwu
5 2 20 liusanjie
6 2 23 wumingshi
7 1 10 hh
按gid分组取每组第一个数据的SQL:
SELECT * FROM test WHERE age IN ( SELECT MAX(age) FROM test GROUP BY gid )
注:最小值则用MIN.
按gid分组取每组最大前N个数据的SQL:
select * from test where ( select count(*) from test as f where f.gid = test.gid and f.age > test.age )<= N-1;
注:最小N个数据则把括号里的>变成<.