Recipe 8.5. Finding Values Associated with Minimum and Maximum Values

Problem

You want to know the values for other columns in the row that contains a minimum or maximum value.

Solution

Use two statements and a user-defined variable. Or use a subquery. Or use a join.

Discussion

MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠) find the endpoints of a range of values, but sometimes when finding a minimum or maximum value, you're also interested in other values from the row in which the value occurs. For example, you can find the largest state population like this:

mysql> SELECT MAX(pop) FROM states; +----------+ | MAX(pop) | +----------+ | 35893799 | +----------+ 

But that doesn't show you which state has this population. The obvious attempt at getting that information looks like this:

mysql> SELECT MAX(pop), name FROM states WHERE pop = MAX(pop); ERROR 1111 (HY000): Invalid use of group function 

Probably everyone tries something like that sooner or later, but it doesn't work. Aggregate functions such as MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠) cannot be used in WHERE clauses, which require expressions that apply to individual rows. The intent of the statement is to determine which row has the maximum population value, and then display the associated state name. The problem is that while you and I know perfectly well what we mean by writing such a thing, it makes no sense at all to MySQL. The statement fails because MySQL uses the WHERE clause to determine which rows to select, but it knows the value of an aggregate function only after selecting the rows from which the function's value is determined! So, in a sense, the statement is self-contradictory. You can solve this problem by saving the maximum population value in a user-defined variable and then comparing rows to the variable value:

mysql> SET @max = (SELECT MAX(pop) FROM states); mysql> SELECT pop AS 'highest population', name FROM states                 WHERE pop = @max; +--------------------+------------+ | highest population | name       | +--------------------+------------+ |           35893799 | California | +--------------------+------------+ 

For a single-statement solution, use a subquery in the WHERE clause that returns the maximum population value:

mysql> SELECT pop AS 'highest population', name FROM states     -> WHERE pop = (SELECT MAX(pop) FROM states); +--------------------+------------+ | highest population | name       | +--------------------+------------+ |           35893799 | California | +--------------------+------------+ 

This technique also works even if the minimum or maximum value itself isn't actually contained in the row, but is only derived from it. If you want to know the length of the shortest verse in the King James Version, that's easy to find:

mysql> SELECT MIN(CHAR_LENGTH(vtext)) FROM kjv; +-------------------------+ | MIN(CHAR_LENGTH(vtext)) | +-------------------------+ |                      11 | +-------------------------+ 

If you want to know, "What verse is that?" do this instead:

mysql> SELECT bname, cnum, vnum, vtext FROM kjv     -> WHERE CHAR_LENGTH(vtext) = (SELECT MIN(CHAR_LENGTH(vtext)) FROM kjv); +-------+------+------+-------------+ | bname | cnum | vnum | vtext       | +-------+------+------+-------------+ | John  |   11 |   35 | Jesus wept. | +-------+------+------+-------------+ 

Yet another way to select other columns from rows containing a minimum or maximum value is to use a join. Select the value into another table, and then join it to the original table to select the row that matches the value. To find the row for the state with the highest population, use a join like this:

mysql> CREATE TABLE t SELECT MAX(pop) as maxpop FROM states; mysql> SELECT states.* FROM states INNER JOIN t ON states.pop = t.maxpop; +------------+--------+------------+----------+ | name       | abbrev | statehood  | pop      | +------------+--------+------------+----------+ | California | CA     | 1850-09-09 | 35893799 | +------------+--------+------------+----------+ 

See Also

For more information about joins, see Chapter 12, in particular, Section 12.6, which further discusses the problem of finding rows that contain groupwise minimum or maximum values.