如何获得包含每个分组集的最大值的行?

我见过这个问题的一些过于复杂的变体,没有一个有好的答案。我试着把最简单的例子放在一起:

给一个这样的表,有个人、组和年龄列,你如何得到每组中年龄最大的人?(一组中平局的结果应按首字母顺序排列)

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 * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

其他回答

Axiac的解决方案最终是最适合我的。然而,我有一个额外的复杂性:一个计算出的“最大值”,来自两列。

让我们用同样的例子:我想要每组中年龄最大的人。如果有人年龄相当,就拿最高的那个人来说吧。

我必须执行左连接两次来得到这个行为:

SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height 
WHERE o2.Height is NULL;

希望这能有所帮助!我想应该有更好的方法来做到这一点…

使用CTEs -常用表表达式:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

在mysql中有一个超级简单的方法:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

这是可行的,因为在mysql中,你不允许聚合非group-by列,在这种情况下,mysql只返回第一行。解决方案是首先对数据进行排序,这样对于每个组,您想要的行是第一个,然后按照您想要的值的列进行分组。

避免了试图找到max()等复杂的子查询,也避免了当有多个具有相同最大值的行时返回多行的问题(因为其他答案会这样做)。

注意:这是一个仅适用于mysql的解决方案。我所知道的所有其他数据库都会抛出SQL语法错误,提示“未聚合的列未按子句列在组中”或类似的信息。因为这个解决方案使用了未记录的行为,所以更谨慎的人可能想要包含一个测试来断言,如果MySQL的未来版本改变了这个行为,它仍然可以工作。

5.7版本更新:

从5.7版开始,sql模式设置默认只包含了ONLY_FULL_GROUP_BY,因此要使其生效,您必须没有此选项(编辑服务器的选项文件以删除此设置)。

这种方法的好处是允许您根据不同的列进行排序,而不会破坏其他数据。如果您试图用一列物品列出订单,首先列出最重的,那么这种方法非常有用。

来源: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;

你也可以试试

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;