Actually, the retrieved data is not displayed in a mere
random order. If unsorted, data is typically displayed in the
order in which it appears in the underlying tables. This could
be the order in which the data was added to the tables
initially. However, if data was subsequently updated or
deleted, the order is affected by how MySQL reuses reclaimed
storage space. The end result is that you cannot (and should
not) rely on the sort order if you do not explicitly control
it. Relational database design theory states that the sequence
of retrieved data cannot be assumed to have significance if
ordering was not explicitly specified.
Tip
Sorting by Nonselected Columns More
often than not, the columns used in an ORDER
BY clause are ones that were selected for
display. However, this is actually not required, and
it is perfectly legal to sort data by a column that
is not retrieved.
Sorting by Multiple Columns
It is often necessary to sort data by more than one
column. For example, if you are displaying an employee
list, you might want to display it sorted by last name and
first name (first sort by last name, and then within each
last name sort by first name). This would be useful if
there are multiple employees with the same last name.
To sort by multiple columns, simply specify the column
names separated by commas (just as you do when you are
selecting multiple columns).
The following code retrieves three columns and sorts the
results by two of them first by price and then by name.
• Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
• Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
| FU1 | 3.42 | Fuses |
| SLING | 4.49 | Sling |
| ANV01 | 5.99 | .5 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV02 | 9.99 | 1 ton anvil |
| FB | 10.00 | Bird seed |
| TNT2 | 10.00 | TNT (5 sticks) |
| DTNTR | 13.00 | Detonator |
| ANV03 | 14.99 | 2 ton anvil |
| JP1000 | 35.00 | JetPack 1000 |
| SAFE | 50.00 | Safe |
| JP2000 | 55.00 | JetPack 2000 |
+---------+------------+----------------+
It is important to understand that when you are sorting by
multiple columns, the sort sequence is exactly as
specified. In other words, using the output in the previous
example, the products are sorted by the prod_name column
only when multiple rows have the same prod_price value. If
all the values in the prod_price column had been unique, no
data would have been sorted by prod_name.
Specifying Sort Direction
Data sorting is not limited to ascending sort orders
(from A to Z). Although this is the default sort order, the
ORDER BY clause can also be used to sort in descending
order (from Z to A). To sort by descending order, the
keyword DESC must be specified.
The following example sorts the products by price in
descending order (most expensive first):
• Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

• Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| JP2000 | 55.00 | JetPack 2000 |
| SAFE | 50.00 | Safe |
| JP1000 | 35.00 | JetPack 1000 |
| ANV03 | 14.99 | 2 ton anvil |
| DTNTR | 13.00 | Detonator |
| TNT2 | 10.00 | TNT (5 sticks) |
| FB | 10.00 | Bird seed |
| ANV02 | 9.99 | 1 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV01 | 5.99 | .5 ton anvil |
| SLING | 4.49 | Sling |
| FU1 | 3.42 | Fuses |
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
+---------+------------+----------------+
But what if you were to sort by multiple columns? The
following example sorts the products in descending order
(most expensive first), plus product name:
• Input
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;

• Output
+---------+------------+----------------+
| prod_id | prod_price | prod_name |
+---------+------------+----------------+
| JP2000 | 55.00 | JetPack 2000 |
| SAFE | 50.00 | Safe |
| JP1000 | 35.00 | JetPack 1000 |
| ANV03 | 14.99 | 2 ton anvil |
| DTNTR | 13.00 | Detonator |
| FB | 10.00 | Bird seed |
| TNT2 | 10.00 | TNT (5 sticks) |
| ANV02 | 9.99 | 1 ton anvil |
| OL1 | 8.99 | Oil can |
| ANV01 | 5.99 | .5 ton anvil |
| SLING | 4.49 | Sling |
| FU1 | 3.42 | Fuses |
| FC | 2.50 | Carrots |
| TNT1 | 2.50 | TNT (1 stick) |
+---------+------------+----------------+
• Analysis
The DESC keyword only applies to the column name that
directly precedes it. In the previous example, DESC was
specified for the prod_price column, but not for the
prod_name column. Therefore, the prod_price column is
sorted in descending order, but the prod_name column
(within each price) is still sorted in standard ascending
order.
Tip
Sorting Descending on
Multiple Columns If you want to sort descending on multiple
columns, be sure each column has its own DESC
keyword.
The opposite of DESC is
ASC (for ascending), which may be specified to sort in
ascending order. In practice, however, ASC is not usually
used because ascending order is the default sequence (and
is assumed if neither ASC nor DESC are
specified).
Case Sensitivity and Sort
Orders When you are sorting textual data, is A the same as
a? And does a come before B or after Z? These are not
theoretical questions, and the answers depend on how the
database is set up.
In dictionary sort order,
A is treated the same as a, and that is the default
behavior in MySQL (and indeed most DBMSs). However,
administrators can change this behavior if needed. (If your
database contains lots of foreign language characters, this
might become necessary.)
The key here is that, if
you do need an alternate sort order, you cannot accomplish
it with a simple ORDER BY clause. You must contact your
database administrator.
Using a combination of ORDER BY and LIMIT, it is
possible to find the highest or lowest value in a column.
The following example demonstrates how to find the value of
the most expensive item:
• Input
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

• Output
+------------+
| prod_price |
+------------+
| 55.00 |
+------------+
• Analysis
prod_price DESC ensures that rows are retrieved from
most to least expensive, and LIMIT 1 tells MySQL to just
return one row.
Caution
Position of ORDER BY
Clause When specifying an ORDER BY clause, be sure that it
is after the FROM clause. If LIMIT is used, it must come
after ORDER BY. Using clauses out of order will generate an
error message.
Summary
In this tutorial, you learned how to sort retrieved data
using the SELECT statement's ORDER BY clause. This clause,
which must be the last in the SELECT statement, can be used
to sort data on one or more columns as needed.
[Next]