October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You want to sort the result of a summary query.
Use an ORDER BY clause—if
GROUP BY
doesn’t produce the desired sort order.
In MySQL, GROUP BY not only groups,
it sorts. Thus there is often no need for an ORDER
BY clause in a summary query. 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, run this query:
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 | +-------+------+---------+ ...