Database Reference
In-Depth Information
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. Even if you could specify storage order, it wouldn't help you
if you want results in different orders at different times.
When you select rows, they're returned from the database in whatever order the server
happens to use. A relational database makes no guarantee about the order in which it
returns rows—unless you tell it how, by adding an
ORDER
BY
clause to your
SELECT
statement. Without
ORDER
BY
, you may find that the retrieval order changes over time
as you modify the table contents. With an
ORDER
BY
clause, MySQL always sorts rows as
you indicate.
ORDER
BY
has the following general characteristics:
•
You can sort using one or more column or expression values.
• You can sort columns independently in 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 (see Recipes
15.7
and
15.8
).
The following examples demonstrate how to sort on a single column or multiple col‐
umns 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 to demonstrate 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 | 2014-07-30 | 152 |
| 9 | Ben | 2014-08-02 | 79 |
| 5 | Ben | 2014-07-29 | 131 |
| 8 | Henry | 2014-08-01 | 197 |
| 6 | Henry | 2014-07-26 | 115 |
| 4 | Henry | 2014-07-27 | 96 |
| 3 | Henry | 2014-07-29 | 300 |
| 10 | Henry | 2014-07-30 | 203 |
| 7 | Suzi | 2014-08-02 | 502 |
| 2 | Suzi | 2014-07-29 | 391 |
+--------+-------+------------+-------+
