如何获得包含每个分组集的最大值的行?
我见过这个问题的一些过于复杂的变体，没有一个有好的答案。我试着把最简单的例子放在一起:
给一个这样的表，有个人、组和年龄列，你如何得到每组中年龄最大的人?(一组中平局的结果应按首字母顺序排列)
Person | Group | Age
---
Bob | 1 | 32
Jill | 1 | 34
Shawn| 1 | 42
Jake | 2 | 29
Paul | 2 | 36
Laura| 2 | 39
期望结果集:
Shawn | 1 | 42
Laura | 2 | 39
当前回答
让桌子的名字是人
select O.* -- > O for oldest table
from people O , people T
where O.grp = T.grp and
O.Age =
(select max(T.age) from people T where O.grp = T.grp
group by T.grp)
group by O.grp;
其他回答
你也可以试试
SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;
SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL
group by o.Group
with CTE as
(select Person,
[Group], Age, RN= Row_Number()
over(partition by [Group]
order by Age desc)
from yourtable)`
`select Person, Age from CTE where RN = 1`
这种方法的好处是允许您根据不同的列进行排序，而不会破坏其他数据。如果您试图用一列物品列出订单，首先列出最重的，那么这种方法非常有用。
来源:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html function_group-concat
SELECT person, group,
GROUP_CONCAT(
DISTINCT age
ORDER BY age DESC SEPARATOR ', follow up: '
)
FROM sql_table
GROUP BY group;
如果需要mytable中的ID(以及所有的列)
SELECT
*
FROM
mytable
WHERE
id NOT IN (
SELECT
A.id
FROM
mytable AS A
JOIN mytable AS B ON A. GROUP = B. GROUP
AND A.age < B.age
)
