Get the maximum value record in each grouped SQL result
P粉186904731
2023-08-20 18:10:21
<p>How to get the row containing the maximum value for each grouping? </p>
<p>I saw a few overly complex variations, but none gave a good answer. I tried the simplest example: </p>
<p>Given the table below, which contains columns for people, groups, and ages, how do you get the oldest person in each group? (A tie within a group should give the first result in alphabetical order) </p>
<pre class="brush:php;toolbar:false;">Person | Group | Age
---
Bob | 1 | 32
Jill | 1 | 34
Shawn| 1 | 42
Jake | 2 | 29
Paul | 2 | 36
Laura| 2 | 39</pre>
<p>Desired result set: </p>
<pre class="brush:php;toolbar:false;">Shawn | 1 | 42
Laura | 2 | 39</pre>
<p><br /></p>
The correct solution is:
How it works:
It matches each row in
o
with all rows inb
that have the sameGroup
column value and a largerAge
column value lines to match. Any row ino
that does not have the maximum value in its group in theAge
column is matched by one or more rows inb
.
TheLEFT JOIN
Make it match the oldest person in the group (including those who are alone) with a rowNULL
fromb
( 'There is no older age in the group').Using
INNER JOIN
will cause these rows to not match and they will be ignored.WHERE
clause retains only rows withNULL
in fields extracted fromb
. They are the eldest in each group.Further reading
This solution and many others are explained in detail in the book "SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming".
There is a super simple way to do this in mysql:
This method works because in mysql, you can not aggregate non-group by columns, in which case mysql only returns the first row . The solution is to first sort the data in the order you want and then group by the columns you want.
You avoid the problem of complex subqueries trying to find
max()
etc., and also avoid the problem of returning multiple rows when there are multiple rows with the same max value (other answers do this Do).NOTE: This is a only solution for mysql. All other databases I know of will throw a SQL syntax error with the error message "Non-aggregate column not listed in group by clause" or something similar. Because this solution uses undocumented behavior, a more prudent person might want to include a test to ensure it still works if a future version of MySQL changes this behavior.
Version 5.7 Update:
Since version 5.7, the
sql-mode
setting containsONLY_FULL_GROUP_BY
by default, so to make it work you must Do not use this option (edit the server's options file to remove this setting).