Using ORDER BY to Sort Query Results

6.2.1 Problem

Output from a query doesn't come out in the order you want.

6.2.2 Solution

Add an ORDER BY clause to the query.

6.2.3 Discussion

The contents of the driver_log and mail tables shown in the chapter introduction are disorganized and difficult to make any sense of. The exception is that the values in the id and t columns are in order, but that's just coincidental. Rows do tend to be returned from a table in the order they were originally inserted, but only until the table is subjected to delete and update operations. Rows inserted after that are likely to be returned in the middle of the result set somewhere. Many MySQL users notice this disturbance in row retrieval order, which leads them to ask, "How can I store rows in my table so they come out in a particular order when I retrieve them?" The answer to this question is that it's the wrong question. Storing rows is the server's job and you should let the server do it. (Besides, even if you could specify storage order, how would that help you if you wanted to see results sorted in different orders at different times?)

When you select records, they're pulled out of the database and returned in whatever order the server happens to use. This may change, even for queries that don't sort rows, depending on which index the server happens to use when it executes a query, because the index can affect the retrieval order. Even if your rows appear to come out in the proper order naturally, a relational database makes no guarantee about the order in which it returns rowsunless you tell it how. To arrange the rows from a query result into a specific order, sort them by adding an ORDER BY clause to your SELECT statement. Without ORDER BY, you may find that the retrieval order changes when you modify the contents of your table. With an ORDER BY clause, MySQL will always sort rows the way you indicate.

ORDER BY has the following general characteristics:

• You can sort using a single column of values or multiple columns
• You can sort any column in either ascending order (the default) or descending order
• You can refer to sort columns by name, by their position within the output column list, or by using an alias

This section shows some basic sorting techniques, and the following sections illustrate how to perform more complex sorts. Paradoxically, you can even use ORDER BY to disorder a result set, which is useful for randomizing the rows, or (in conjunction with LIMIT) for picking a row at random from a result set. Those uses for ORDER BY are described in Chapter 13.

6.2.4 Naming the Sort Columns and Specifying Sorting Direction

The following set of examples demonstrates how to sort on a single column or multiple columns and how to sort in ascending or descending order. The examples select the rows in the driver_log table but sort them in different orders so that you can compare the effect of the different ORDER BY clauses.

This query produces a single-column sort using the driver name:

mysql> SELECT * FROM driver_log ORDER BY name;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2001-11-30 | 152 |
| 5 | Ben | 2001-11-29 | 131 |
| 9 | Ben | 2001-12-02 | 79 |
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 6 | Henry | 2001-11-26 | 115 |
| 8 | Henry | 2001-12-01 | 197 |
| 10 | Henry | 2001-11-30 | 203 |
| 2 | Suzi | 2001-11-29 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
+--------+-------+------------+-------+

The default sort direction is ascending. You can make the direction for an ascending sort explicit by adding ASC after the sorted column's name:

SELECT * FROM driver_log ORDER BY name ASC;

The opposite (or reverse) of ascending order is descending order, specified by adding DESC after the sorted column's name:

mysql> SELECT * FROM driver_log ORDER BY name DESC;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 2 | Suzi | 2001-11-29 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 6 | Henry | 2001-11-26 | 115 |
| 8 | Henry | 2001-12-01 | 197 |
| 10 | Henry | 2001-11-30 | 203 |
| 1 | Ben | 2001-11-30 | 152 |
| 5 | Ben | 2001-11-29 | 131 |
| 9 | Ben | 2001-12-02 | 79 |
+--------+-------+------------+-------+

If you closely examine the output from the queries just shown, you'll notice that although the rows are sorted by name, the rows for any given name aren't in any special order (The trav_date values aren't in date order for Henry or Ben, for example.) That's because MySQL doesn't sort something unless you tell it to:

• The overall order of rows returned by a query is indeterminate unless you specify an ORDER BY clause.
• In the same way, within a group of rows that sort together based on the values in a given column, the order of values in other columns also is indeterminate unless you name them in the ORDER BY clause.

To more fully control output order, specify a multiple-column sort by listing each column to use for sorting, separated by commas. The following query sorts in ascending order by name and by trav_date within the rows for each name:

mysql> SELECT * FROM driver_log ORDER BY name, trav_date;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 5 | Ben | 2001-11-29 | 131 |
| 1 | Ben | 2001-11-30 | 152 |
| 9 | Ben | 2001-12-02 | 79 |
| 6 | Henry | 2001-11-26 | 115 |
| 4 | Henry | 2001-11-27 | 96 |
| 3 | Henry | 2001-11-29 | 300 |
| 10 | Henry | 2001-11-30 | 203 |
| 8 | Henry | 2001-12-01 | 197 |
| 2 | Suzi | 2001-11-29 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
+--------+-------+------------+-------+

Multiple-column sorts can be descending as well, but DESC must be specified after each column name to perform a fully descending sort:

mysql> SELECT * FROM driver_log ORDER BY name DESC, trav_date DESC;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 7 | Suzi | 2001-12-02 | 502 |
| 2 | Suzi | 2001-11-29 | 391 |
| 8 | Henry | 2001-12-01 | 197 |
| 10 | Henry | 2001-11-30 | 203 |
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 6 | Henry | 2001-11-26 | 115 |
| 9 | Ben | 2001-12-02 | 79 |
| 1 | Ben | 2001-11-30 | 152 |
| 5 | Ben | 2001-11-29 | 131 |
+--------+-------+------------+-------+

Multiple-column ORDER BY clauses can perform mixed-order sorting where some columns are sorted in ascending order and others in descending order. The following query sorts by name in descending order, then by trav_date in ascending order for each name:

mysql> SELECT * FROM driver_log ORDER BY name DESC, trav_date;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 2 | Suzi | 2001-11-29 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
| 6 | Henry | 2001-11-26 | 115 |
| 4 | Henry | 2001-11-27 | 96 |
| 3 | Henry | 2001-11-29 | 300 |
| 10 | Henry | 2001-11-30 | 203 |
| 8 | Henry | 2001-12-01 | 197 |
| 5 | Ben | 2001-11-29 | 131 |
| 1 | Ben | 2001-11-30 | 152 |
| 9 | Ben | 2001-12-02 | 79 |
+--------+-------+------------+-------+

Should You Sort Query Results Yourself?

If you're issuing a SELECT query from within one of your own programs, you can retrieve an unsorted result set into a data structure, then sort the data structure using your programming language. But why reinvent the wheel? The MySQL server is built to sort efficiently, and you may as well let it do its job.

A possible exception to this principle occurs when you need to sort a set of rows several different ways. In this case, rather than issuing several queries that differ only in the ORDER BY clause, it might be faster to retrieve the records once, and resort them as necessary within your program.

6.2.5 More Ways to Refer to Sort Columns

The ORDER BY clauses in the queries shown thus far refer to the sorted columns by name. You can also name the columns by their positions within the output column list or by using aliases. Positions within the output list begin with 1. The following query sorts results by the third output column, miles:

mysql> SELECT name, trav_date, miles FROM driver_log ORDER BY 3;
+-------+------------+-------+
| name | trav_date | miles |
+-------+------------+-------+
| Ben | 2001-12-02 | 79 |
| Henry | 2001-11-27 | 96 |
| Henry | 2001-11-26 | 115 |
| Ben | 2001-11-29 | 131 |
| Ben | 2001-11-30 | 152 |
| Henry | 2001-12-01 | 197 |
| Henry | 2001-11-30 | 203 |
| Henry | 2001-11-29 | 300 |
| Suzi | 2001-11-29 | 391 |
| Suzi | 2001-12-02 | 502 |
+-------+------------+-------+

If an output column has an alias, you can refer to the alias in the ORDER BY clause:

mysql> SELECT name, trav_date, miles AS distance FROM driver_log
 -> ORDER BY distance;
+-------+------------+----------+
| name | trav_date | distance |
+-------+------------+----------+
| Ben | 2001-12-02 | 79 |
| Henry | 2001-11-27 | 96 |
| Henry | 2001-11-26 | 115 |
| Ben | 2001-11-29 | 131 |
| Ben | 2001-11-30 | 152 |
| Henry | 2001-12-01 | 197 |
| Henry | 2001-11-30 | 203 |
| Henry | 2001-11-29 | 300 |
| Suzi | 2001-11-29 | 391 |
| Suzi | 2001-12-02 | 502 |
+-------+------------+----------+

Aliases have an advantage over positionally specified columns in ORDER BY clause. If you use positions for sorting, but then revise the query to change the output column list, you may need to revise the position numbers in the ORDER BY clause as well. If you use aliases, this is unnecessary. (But note that some database engines do not support use of aliases in ORDER BY clauses, so this feature is not portable.)

Columns specified by positions or by aliases can be sorted in either ascending or descending order, just like named columns:

mysql> SELECT name, trav_date, miles FROM driver_log ORDER BY 3 DESC;
+-------+------------+-------+
| name | trav_date | miles |
+-------+------------+-------+
| Suzi | 2001-12-02 | 502 |
| Suzi | 2001-11-29 | 391 |
| Henry | 2001-11-29 | 300 |
| Henry | 2001-11-30 | 203 |
| Henry | 2001-12-01 | 197 |
| Ben | 2001-11-30 | 152 |
| Ben | 2001-11-29 | 131 |
| Henry | 2001-11-26 | 115 |
| Henry | 2001-11-27 | 96 |
| Ben | 2001-12-02 | 79 |
+-------+------------+-------+