Databases Reference
In-Depth Information
| 3.46 | Spinning Around |
| 3.44 | Mesh |
| 3.42 | Cries And Whispers |
| 3.40 | Under The Influence Of Love |
| 3.40 | Ventilator Blues |
| 3.26 | Straight To The Man |
| 3.20 | Dreams Never End |
| 3.08 | Happy |
| 3.00 | Shake Your Hips |
| 2.99 | Your Star Will Shine |
| 2.97 | Sweet Black Angel |
| 2.90 | I Just Want To See His Face |
| 2.81 | Full Nelson |
| 2.78 | Jangling Jack |
| 2.38 | Rip This Joint |
| 1.81 | In A Silent Way |
| 1.34 | Intermission By Alan Wise [Olympia, Paris 12/11/01] |
+------+------------------------------------------------------------+
24 rows in set (0.06 sec)
In this example, the rows are sorted by descending
time
and, when there's a collision,
by ascending
track_name
. We've used the optional keyword
ASC
to indicate an ascending
sort key. Whenever we sort, ascending order is assumed if the
DESC
keyword isn't used.
You don't need to explicitly include the
ASC
keyword, but including it does help to make
the statement's behavior more obvious. Notice also that we've included a
WHERE
clause;
using
WHERE
and
ORDER BY
together is very common, and
WHERE
always appears before
ORDER BY
in the
SELECT
statement.
If a collision of values occurs, and you don't specify another sort key, the sort order is
undefined. This may not be important for you; you may not care about the order in
which two customers with the identical name “John A. Smith” appear. A common
source of collisions is string sorting, where MySQL ignores the case of characters. For
example, the strings
john
,
John
, and
JOHN
are treated as identical in the
ORDER BY
process.
If you do want sorting to behave like ASCII does (where uppercase comes before low-
ercase), then you can add a
BINARY
keyword to your sort as follows:
mysql>
SELECT * FROM artist ORDER BY BINARY artist_name;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 6 | Kylie Minogue |
| 3 | Miles Davis |
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
+-----------+---------------------------+
6 rows in set (0.01 sec)
Because there are no case collisions in the
music
database, this example doesn't do
anything different from the example without the
BINARY
keyword.
