Databases Reference
In-Depth Information
| 2.90 | I Just Want To See His Face |
| 2.97 | Sweet Black Angel |
| 2.99 | Your Star Will Shine |
| 3.00 | Shake Your Hips |
| 3.08 | Happy |
| 3.20 | Dreams Never End |
| 3.26 | Straight To The Man |
| 3.40 | Under The Influence Of Love |
| 3.40 | Ventilator Blues |
| 3.42 | Cries And Whispers |
| 3.44 | Mesh
...
We've shown only part of the 153-row output. Notice that there's a collision of track
times where the length is 3.40. In this case, the second sort key,
track_name
, is used to
resolve the collision so that “Under the Influence of Love” appears before “Ventilator
Blues.” You'll find you often use multiple columns in an
ORDER BY
clause when you're
sorting people's names, where typically you'll use something like
ORDER BY surname,
firstname, secondname
.
You can also sort in descending order, and you can control this behavior for each sort
key. Suppose you want to sort the artists by descending alphabetical order. You type
this:
mysql>
SELECT artist_name FROM artist ORDER BY artist_name DESC;
+---------------------------+
| artist_name |
+---------------------------+
| The Stone Roses |
| The Rolling Stones |
| Nick Cave & The Bad Seeds |
| New Order |
| Miles Davis |
| Kylie Minogue |
+---------------------------+
6 rows in set (0.00 sec)
The
DESC
keyword specifies that the preceding sort key (in this case,
artist_name
) should
be sorted in descending order. You can use a mixture of ascending and descending
orders when multiple sort keys are used. For example, you can sort by descending
time
and alphabetically increasing
track_name
:
mysql>
SELECT time, track_name FROM track
-> WHERE time < 3.6
-> ORDER BY time DESC, track_name ASC;
+------+------------------------------------------------------------+
| time | track_name |
+------+------------------------------------------------------------+
| 3.57 | Casino Boogie |
| 3.57 | Procession [Polytechnic of Central London, London 6/12/85] |
| 3.56 | Your Disco Needs You |
| 3.55 | I'm So High |
| 3.55 | On A Night Like This |
| 3.54 | Mr. Pastorius |
