The ORDER BY Clause

So far, you have seen that data is fetched from the database in no particular order. To specify the sorting on the result of a query, you can add an ORDER BY clause.

Sorting on a Single Column

The following example retrieves all the products in order of price. The keywords ORDER BY are followed by the name of the column on which you want to sort.

 mysql> SELECT *     -> FROM products     -> ORDER BY price; +------+----------------+--------+-------+ | code | name           | weight | price | +------+----------------+--------+-------+ | MINI | Small product  |   1.50 |  5.99 | | MIDI | Medium product |   4.50 |  9.99 | | MAXI | Large product  |   8.00 | 15.99 | +------+----------------+--------+-------+ 3 rows in set (0.00 sec) 

Sort Columns

The sort column specified in ORDER BY does not actually have to appear in the list of columns after the SELECT keyword. You can, therefore, specify a sort order using a column that is not retrieved by the query.

When you want to add sorting to a query that is also filtered, the ORDER BY clause must appear after the WHERE clause. This example finds all the contacts for one customer sorted on the last name.

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE customer_code = 'SCICORP'     -> ORDER BY last_name;  +------------+-----------+ | first_name | last_name | +------------+-----------+ | Marie      | Curie     | | Charles    | Darwin    | | Albert     | Einstein  | | Benjamin   | Franklin  | +------------+-----------+ 4 rows in set (0.00 sec) 

Sorting on Multiple Columns

The ORDER BY clause is formed in a similar way to the first line of a SELECT statement. If you want to specify a sort order that involves more than one column in the query, separate the column names with a comma.

The following query fetches data from the orders table in date order. The second sort column, customer_code, is used to specify the sorting when the values of order_date are the same.

 mysql> SELECT order_date, customer_code     -> FROM orders     -> ORDER BY order_date, customer_code; +------------+---------------+ | order_date | customer_code | +------------+---------------+ | 2006-01-23 | PRESINC       | | 2006-01-23 | SCICORP       | | 2006-01-26 | PRESINC       | | 2006-02-01 | MUSGRP        | | 2006-02-02 | MUSGRP        | | 2006-02-02 | SCICORP       | | 2006-02-05 | SCICORP       | +------------+---------------+ 7 rows in set (0.00 sec) 

Specifying Sort Order

By default, the ordering on a column specified in the ORDER BY clause is done in ascending order, either numerically or alphabetically, depending on the data type of the column.

To specify a descending sort direction, use the DESC keyword. The following example sorts the data from the products table with the heaviest at the top of the list.

 mysql> SELECT *     -> FROM products     -> ORDER BY weight DESC; +------+----------------+--------+-------+ | code | name           | weight | price | +------+----------------+--------+-------+ | MAXI | Large product  |   8.00 | 15.99 | | MIDI | Medium product |   4.50 |  9.99 | | MINI | Small product  |   1.50 |  5.99 | +------+----------------+--------+-------+ 3 rows in set (0.00 sec) 

Ascending Order

To explicitly specify ascending sort order, use the ASC keyword in place of DESC. The ASC keyword is optional, but it can be useful to indicate the sort order clearly in a query.

Multiple Sort Orders

When you specify more than one sort column, the keywords ASC and DESC can be used after each column name. You must specify the order direction for each sort in turn.