Recipe 8.14. Finding Smallest or Largest Summary Values
ProblemYou want to compute per-group summary values but display only the smallest or largest of them. SolutionAdd a LIMIT clause to the statement. DiscussionMIN( ) 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) AS letter, COUNT(*) AS count FROM states -> GROUP BY letter ORDER BY count DESC LIMIT 1; +--------+-------+ | letter | count | +--------+-------+ | M | 8 | +--------+-------+ But eight state names also begin with N. If you need to know all most-frequent values when there may be more than one of them, find the maximum count first, and then select those values with a count that matches the maximum: mysql> SET @max = (SELECT COUNT(*) FROM states -> GROUP BY LEFT(name,1) ORDER BY COUNT(*) DESC LIMIT 1); mysql> SELECT LEFT(name,1) AS letter, COUNT(*) AS count FROM states -> GROUP BY letter HAVING count = @max; +--------+-------+ | letter | count | +--------+-------+ | M | 8 | | N | 8 | +--------+-------+ Alternatively, put the maximum-count calculation in a subquery and combine the statements into one: mysql> SELECT LEFT(name,1) AS letter, COUNT(*) AS count FROM states -> GROUP BY letter HAVING count = -> (SELECT COUNT(*) FROM states -> GROUP BY LEFT(name,1) ORDER BY COUNT(*) DESC LIMIT 1); +--------+-------+ | letter | count | +--------+-------+ | M | 8 | | N | 8 | +--------+-------+ |
