Recipe 7.1. Using ORDER BY to Sort Query Results


Problem

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

Solution

Add an ORDER BY clause to the query to sort the result rows.

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's the wrong question." Storing rows is the server's job, and you should let the server do it. Besides, even if you can specify storage order, how would that help you if you want to see results sorted in different orders at different times?

When you select rows, they're pulled out of the database and returned in whatever order the server happens to use. This order might change, even for statements that don't sort rows, depending on which index the server happens to use when it executes a statement, 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 or by using an alias.

This section shows some basic sorting techniques, such as how to name the sort columns and specify the sort direction. 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.

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   | 2006-08-30 |   152 | |      9 | Ben   | 2006-09-02 |    79 | |      5 | Ben   | 2006-08-29 |   131 | |      8 | Henry | 2006-09-01 |   197 | |      6 | Henry | 2006-08-26 |   115 | |      4 | Henry | 2006-08-27 |    96 | |      3 | Henry | 2006-08-29 |   300 | |     10 | Henry | 2006-08-30 |   203 | |      7 | Suzi  | 2006-09-02 |   502 | |      2 | Suzi  | 2006-08-29 |   391 | +--------+-------+------------+-------+ 

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  | 2006-08-29 |   391 | |      7 | Suzi  | 2006-09-02 |   502 | |     10 | Henry | 2006-08-30 |   203 | |      8 | Henry | 2006-09-01 |   197 | |      6 | Henry | 2006-08-26 |   115 | |      4 | Henry | 2006-08-27 |    96 | |      3 | Henry | 2006-08-29 |   300 | |      5 | Ben   | 2006-08-29 |   131 | |      9 | Ben   | 2006-09-02 |    79 | |      1 | Ben   | 2006-08-30 |   152 | +--------+-------+------------+-------+ 

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.

  • 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   | 2006-08-29 |   131 | |      1 | Ben   | 2006-08-30 |   152 | |      9 | Ben   | 2006-09-02 |    79 | |      6 | Henry | 2006-08-26 |   115 | |      4 | Henry | 2006-08-27 |    96 | |      3 | Henry | 2006-08-29 |   300 | |     10 | Henry | 2006-08-30 |   203 | |      8 | Henry | 2006-09-01 |   197 | |      2 | Suzi  | 2006-08-29 |   391 | |      7 | Suzi  | 2006-09-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  | 2006-09-02 |   502 | |      2 | Suzi  | 2006-08-29 |   391 | |      8 | Henry | 2006-09-01 |   197 | |     10 | Henry | 2006-08-30 |   203 | |      3 | Henry | 2006-08-29 |   300 | |      4 | Henry | 2006-08-27 |    96 | |      6 | Henry | 2006-08-26 |   115 | |      9 | Ben   | 2006-09-02 |    79 | |      1 | Ben   | 2006-08-30 |   152 | |      5 | Ben   | 2006-08-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 and 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  | 2006-08-29 |   391 | |      7 | Suzi  | 2006-09-02 |   502 | |      6 | Henry | 2006-08-26 |   115 | |      4 | Henry | 2006-08-27 |    96 | |      3 | Henry | 2006-08-29 |   300 | |     10 | Henry | 2006-08-30 |   203 | |      8 | Henry | 2006-09-01 |   197 | |      5 | Ben   | 2006-08-29 |   131 | |      1 | Ben   | 2006-08-30 |   152 | |      9 | Ben   | 2006-09-02 |    79 | +--------+-------+------------+-------+ 

The ORDER BY clauses in the queries shown thus far refer to the sorted columns by name. You can also name the columns by using aliases. That is, 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   | 2006-09-02 |       79 | | Henry | 2006-08-27 |       96 | | Henry | 2006-08-26 |      115 | | Ben   | 2006-08-29 |      131 | | Ben   | 2006-08-30 |      152 | | Henry | 2006-09-01 |      197 | | Henry | 2006-08-30 |      203 | | Henry | 2006-08-29 |      300 | | Suzi  | 2006-08-29 |      391 | | Suzi  | 2006-09-02 |      502 | +-------+------------+----------+ 

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

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

Should You Sort Query Results Yourself?

If you're issuing a SELECT statement from within one of your own programs, you can retrieve an unsorted result set into a data structure, and 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 rows once, and re-sort them as necessary within your program. The attractiveness of this strategy generally diminishes if your result sets are very large and sorting them will use lots of memory and processing time.





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