Controlling Summary Display Order

7.14.1 Problem

You want to sort the result of a summary query.

7.14.2 Solution

Use an ORDER BY clauseif GROUP BY doesn't produce the desired sort order.

7.14.3 Discussion

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 | total miles |
+-------+------+-------------+
| 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 |
+-------+------+---------+

It's necessary to use an alias (or a column position number) in the ORDER BY clause to refer to the summary values. This is true even for MySQL 3.23.2 and up, which normally allows expressions in an ORDER BY clause; those expressions must refer to individual values, not values computed from a set.

Sometimes you can reorder a summary without an ORDER BY clause by choosing an appropriate GROUP BY expression. For example, if you count how many states joined the Union on each day of the week, grouped by day name, the results will be sorted in lexical order:

mysql> SELECT DAYNAME(statehood), COUNT(*) FROM states
 -> GROUP BY DAYNAME(statehood);
+--------------------+----------+
| DAYNAME(statehood) | COUNT(*) |
+--------------------+----------+
| Friday | 8 |
| Monday | 9 |
| Saturday | 11 |
| Thursday | 5 |
| Tuesday | 6 |
| Wednesday | 11 |
+--------------------+----------+

From this you can see that no state entered the Union on a Sunday, but that becomes apparent only after you stare at the query result for a while. The output would be more easily understood were it sorted into day-of-week order. It's possible to do that by adding an explicit ORDER BY to sort on the numeric day-of-week value, but another way to achieve the same result without ORDER BY is to group by DAYOFWEEK( ) rather than by DAYNAME( ):

mysql> SELECT DAYNAME(statehood), COUNT(*)
 -> FROM states GROUP BY DAYOFWEEK(statehood);
+--------------------+----------+
| DAYNAME(statehood) | COUNT(*) |
+--------------------+----------+
| Monday | 9 |
| Tuesday | 6 |
| Wednesday | 11 |
| Thursday | 5 |
| Friday | 8 |
| Saturday | 11 |
+--------------------+----------+

GROUP BY may not sort output rows in other database systems. To write queries for MySQL that are less likely to need revision when used with other databases, you may find it beneficial to add an explicit ORDER BY clause in all cases.