## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# 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 ...

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required