MySQL Cookbook

Finding Values Associated with Minimum and Maximum Values

Problem

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

Solution

Use two queries and a SQL variable. Or use the “MAX-CONCAT trick.” 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) |
+----------+
| 29760021 |
+----------+

But that doesn’t show you which state has this population. The obvious way to try to get that information is like this:

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

Probably everyone attempts something like that sooner or later, but it doesn’t work, because aggregate functions like MIN( ) and MAX( ) cannot be used in WHERE clauses. The intent of the statement is to determine which record has the maximum population value, then display the associated state name. The problem is that while you and I know perfectly well what we’d mean by writing such a thing, it makes no sense at all to MySQL. The query fails because MySQL uses the WHERE clause to determine which records to select, but it knows the value of an aggregate function only after selecting the records from which the function’s value is determined! ...