MySQL Cookbook

Finding Rows Containing Per-Group Minimum or Maximum Values

Problem

You want to find which record within each group of rows in a table contains the maximum or minimum value for a given column. For example, you want to determine the most expensive painting in your collection for each artist.

Solution

Create a temporary table to hold the per-group maximum or minimum, then join the temporary table with the original one to pull out the matching record for each group.

Discussion

Many questions involve finding largest or smallest values in a particular table column, but it’s also common to want to know what the other values are in the row that contains the value. For example, you can use MAX(pop) to find the largest state population recorded in the states table, but you might also want to know which state has that population. As shown in Recipe 7.6, one way to solve this problem is to use a SQL variable. The technique works like this:

mysql> SELECT @max := MAX(pop) FROM states;
mysql> SELECT * FROM states WHERE pop = @max;
+------------+--------+------------+----------+
| name       | abbrev | statehood  | pop      |
+------------+--------+------------+----------+
| California | CA     | 1850-09-09 | 29760021 |
+------------+--------+------------+----------+

Another way to answer the question is to use a join. First, select the maximum population value into a temporary table:

mysql> CREATE TABLE tmp SELECT MAX(pop) as maxpop FROM states;

Then join the temporary table to the original one to find the record ...