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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

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