MySQL Cookbook, 2nd Edition

Finding Smallest or Largest Summary Values

Problem

You want to compute per-group summary values but display only the smallest or largest of them.

Solution

Add a LIMIT clause to the statement.

Discussion

MIN()and MAX() find the values at the endpoints of a range of values, but if you want to know the extremes of a set of summary values, those functions won’t work. The arguments to MIN() and MAX() cannot be other aggregate functions. For example, you can easily find per-driver mileage totals:

mysql>SELECT name, SUM(miles)
    -> FROM driver_log
    -> GROUP BY name;
+-------+------------+
| name  | SUM(miles) |
+-------+------------+
| Ben   |        362 |
| Henry |        911 |
| Suzi  |        893 |
+-------+------------+

But this doesn’t work if you want to select only the row for the driver with the most miles:

mysql>SELECT name, SUM(miles)
    -> FROM driver_log
    -> GROUP BY name
    -> HAVING SUM(miles) = MAX(SUM(miles));
ERROR 1111 (HY000): Invalid use of group function

Instead, order the rows with the largest SUM() values first, and use LIMIT to select the first row:

mysql>SELECT name, SUM(miles) AS 'total miles'
    -> FROM driver_log
    -> GROUP BY name
    -> ORDER BY 'total miles' DESC LIMIT 1;
+-------+-------------+
| name  | total miles |
+-------+-------------+
| Henry |         911 |
+-------+-------------+

Note that if there is more than one row with the given summary value, a LIMIT 1 query won’t tell you that. For example, you might attempt to ascertain the most common initial letter for state names like this:

mysql>SELECT LEFT(name,1) ...