取得每個分組的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，所以要使其工作，你必須不使用此選項（編輯伺服器的選項檔案以刪除此設定）。