MySQL Cookbook, 2nd Edition

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