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

Question

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

我看到了一些过于复杂的变体，但没有一个给出了好的答案。我尝试了最简单的例子：

给定下面的表格，包含人员、分组和年龄列，如何获取每个分组中年龄最大的人员？（在一个分组内的平局应该给出字母顺序中的第一个结果）

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中的每一行与具有相同Group列值和较大Age列值的b中的所有行进行匹配。任何o中的行如果在Age列中没有其组中的最大值，则会与b中的一行或多行匹配。

LEFT JOIN使其将组中的最年长的人（包括那些独自一人的人）与来自b的一行NULL进行匹配（'组中没有更大的年龄'）。
使用INNER JOIN会使这些行不匹配，并且它们会被忽略。

WHERE子句仅保留从b中提取的字段中具有NULL的行。它们是每个组中最年长的人。

进一步阅读

这个解决方案和许多其他解决方案在书籍《SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming》中有详细解释。

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语法错误，错误信息为“非聚合列未在group by子句中列出”或类似的错误。因为这个解决方案使用了未记录的行为，更谨慎的人可能希望包含一个测试来确保它在MySQL的未来版本更改此行为时仍然有效。

版本5.7更新：

自5.7版本起，sql-mode设置默认包含ONLY_FULL_GROUP_BY，所以要使其工作，你必须不使用此选项（编辑服务器的选项文件以删除此设置）。