Database Reference
In-Depth Information
Solution
Add a
LIMIT
clause to the statement. Or use a user-defined variable or subquery to pick
the appropriate summary.
Discussion
MIN()
and
MAX()
find the values at the endpoints of a set of values, but to find the
endpoints of a set of summary values, those functions won't work. Their argument
cannot be another aggregate function. For example, you can easily find per-driver mile‐
age totals:
mysql>
SELECT name, SUM(miles)
->
FROM driver_log
->
GROUP BY name;
+-------+------------+
| name | SUM(miles) |
+-------+------------+
| Ben | 362 |
| Henry | 911 |
| Suzi | 893 |
+-------+------------+
To select only the row for the driver with the most miles, the following doesn't work:
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)
->
FROM driver_log
->
GROUP BY name
->
ORDER BY SUM(miles) DESC LIMIT 1;
+-------+------------+
| name | SUM(miles) |
+-------+------------+
| Henry | 911 |
+-------+------------+
However, if more than one row has 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(*) FROM states
->
GROUP BY letter ORDER BY COUNT(*) DESC LIMIT 1;
+--------+----------+
| letter | COUNT(*) |
