Controlling Summary Display Order
Problem
You want to sort the result of a summary statement.
Solution
Use an ORDER
BY
clause—if GROUP
BY
doesn’t produce the desired sort
order.
Discussion
In MySQL, GROUP
BY
not only groups, it sorts. Thus, there is
often no need for an ORDER
BY
clause in a summary statement. But you
can still use ORDER
BY
if you want a sort order other than the
one that GROUP
BY
produces by default. For example, to
determine the number of days driven and total miles for each person in
the driver_log
table, use this
statement:
mysql>SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
->FROM driver_log GROUP BY name;
+-------+------+---------+ | name | days | mileage | +-------+------+---------+ | Ben | 3 | 362 | | Henry | 5 | 911 | | Suzi | 2 | 893 | +-------+------+---------+
But that sorts by the names. If you want to sort drivers
according to who drove the most days or miles, add the appropriate
ORDER
BY
clause:
mysql>SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
->FROM driver_log GROUP BY name ORDER BY days DESC;
+-------+------+---------+ | name | days | mileage | +-------+------+---------+ | Henry | 5 | 911 | | Ben | 3 | 362 | | Suzi | 2 | 893 | +-------+------+---------+ mysql>SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
->FROM driver_log GROUP BY name ORDER BY mileage DESC;
+-------+------+---------+ | name | days | mileage | +-------+------+---------+ | Henry | 5 | 911 | | Suzi | 2 | 893 | | Ben | 3 | 362 | +-------+------+---------+
The ORDER ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.