获取每组分组SQL结果的最大值记录

Question

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

关于这个问题，我见过一些过于复杂的变体，但没有一个有好的答案。我试着举一个最简单的例子：

给出一个类似下面的表，包含person,group,和age列，如何得到每个组中最年长的人？（一组内的平局应给出第一个按字母顺序排列的结果）

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  

Answer 1

正确的解决方案是：

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

它将o中的每一行与b中的所有行匹配，这些行在列group中具有相同的值，而在列age中具有更大的值。O中的任何行不具有Age列中其组的最大值，都将匹配B中的一个或多个行。

左联接使它将组中最年长的人（包括组中单独的人）与b中充满null的行（“组中没有最大年龄”）相匹配。
使用内联接使这些行不匹配，并且将忽略它们。

where子句仅保留从b提取的字段中具有nulls的行。他们是每组中年龄最大的人。

SQL反模式：避免数据库编程的陷阱》一书中解释了这种解决方案和许多其他解决方案

Answer 2

在MySQL中有一种超简单的方法来实现这一点：

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

这是因为在mysql中允许不聚合非GROUP-BY列，在这种情况下，mysql只返回第一行。解决方案是首先对数据进行排序，使每组中所需的行位于第一位，然后按所需值的列进行分组。

您可以避免尝试查找max()等的复杂子查询，还可以避免在有多个具有相同最大值的行时返回多个行的问题（其他答案会这样做）

注意：这是一个只支持MySQL的解决方案。我知道的所有其他数据库都会抛出一个SQL语法错误，带有消息“non aggregated columns are not list in the group by子句”或类似的信息。因为这个解决方案使用了未记录的行为，所以如果MySQL的未来版本改变了这个行为，比较谨慎的人可能希望包含一个测试来断言它仍然可以工作。

自5.7版以来，SQL-MODE设置默认包含only_full_group_by，因此要使其正常工作，您必须不具有此选项（编辑服务器的选项文件以删除此设置）。