Chapter 7. Sorting Query Results

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 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 * FROM mail; +---------------------+---------+---------+---------+---------+---------+ | t | srcuser | srchost ...

Get MySQL Cookbook, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.