Recipe 8.13. Controlling Summary Display Order


Problem

You want to sort the result of a summary statement.

Solution

Use an ORDER BY clauseif 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 BY clause in these statements refers to an aggregate value by using an alias. In MySQL 5.0 and up, that is not necessary and you can refer directly to aggregate values in ORDER BY clauses. Before MySQL 5.0, you must alias them and use the alias in the ORDER BY.

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 are 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 | +--------------------+----------+ 

The implicit ordering done by GROUP BY can add overhead to query processing. If you don't care whether output rows are sorted, add an ORDER BY NULL clause to suppress this sorting and eliminate its overhead:

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 | +-------+------+---------+ mysql> SELECT name, COUNT(*) AS days, SUM(miles) AS mileage     -> FROM driver_log GROUP BY name ORDER BY NULL; +-------+------+---------+ | name  | days | mileage | +-------+------+---------+ | Ben   |    3 |     362 | | Suzi  |    2 |     893 | | Henry |    5 |     911 | +-------+------+---------+ 

The sorting done by GROUP BY is a MySQL extension. To write statements for MySQL that are less likely to need revision when used with other database systems, you may find it beneficial to add an explicit ORDER BY clause in all cases.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net