Learning Mysql

| 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 inset 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 lowercase, 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 inset 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.

Download 4.24 Mb.

Share with your friends: