Get the maximum value record in each grouped SQL result

Question

How to get the row containing the maximum value for each grouping?

I saw a few overly complex variations, but none gave a good answer. I tried the simplest example:

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)

Person | Group | Age
---
Bob | 1 | 32
Jill | 1 | 34
Shawn| 1 | 42
Jake | 2 | 29
Paul | 2 | 36
Laura| 2 | 39

Desired result set:

Shawn | 1 | 42
Laura | 2 | 39

Answer 1

The correct solution is:

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

How it works:

It matches each row in o with all rows in b that have the same Group column value and a larger Age column value lines to match. Any row in o that does not have the maximum value in its group in the Age column is matched by one or more rows in b.

LEFT JOIN Make it match the oldest person in the group (including those who are alone) with a row NULL from b( 'There is no older age in the group').
Using INNER JOIN will cause these rows to not match and they will be ignored.

The

WHERE clause retains only rows with NULL in fields extracted from b. 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".

Answer 2

There is a super simple way to do this in mysql:

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

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 contains ONLY_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).