MySQL Cookbook, 4th Edition

Chapter 9. Sorting Query Results

9.0 Introduction

This chapter covers sorting, an extremely important operation for controlling how MySQL displays results from SELECT statements. To sort a query result, add an ORDER BY clause to the query. Without such a clause, MySQL is free to return rows in any order, so sorting helps bring order to disorder and makes query results easier to examine and understand.

You can sort rows of a query result in several ways:

• Using a single column, a combination of columns, or even parts of columns or expression results

• Using ascending or descending order

• Using case-sensitive or case-insensitive string comparisons

• Using temporal ordering

Several examples in this chapter use the driver_log table, which contains columns for recording daily mileage logs for a set of truck drivers:

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

Many other examples use the mail table (used in earlier chapters):

mysql> SELECT t, srcuser, srchost, dstuser, dsthost, size FROM mail; +---------------------+---------+---------+---------+---------+---------+ ...