Basic SQL - Learning MySQL - page 151

changeably with the word-based alternatives if you want to. However, we always use
the word-based versions, as that's what you'll see used in most SQL statements.
ORDER BY Clauses
We've so far discussed how to choose the columns and rows that are returned as part
of the query result, but not how to control how the result is displayed. In a relational
database, the rows in a table form a set; there is no intrinsic order between the rows,
and so we have to ask MySQL to sort the results if we want them in a particular order.
In this section, we explain how to use the ORDER BY clause to do this. Sorting has no
effect on what is returned, and only affects what order the results are returned.
Suppose you want to return a list of the artists in the music database, sorted in alpha-
betical order by the artist_name . Here's what you'd type:
mysql> SELECT * FROM artist ORDER BY 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.03 sec)
The ORDER BY clause indicates that sorting is required, followed by the column that
should be used as the sort key. In this example, we're sorting by alphabetically-as-
cending artist_name . The default sort is case-insensitive and in ascending order, and
MySQL automatically sorts alphabetically because the columns are character strings.
The way strings are sorted is determined by the character set and collation order that
are being used. We discuss these in “Collation and Character Sets.” For most of this
book, we assume that you're using the default settings.
Consider a second example. This time, let's sort the output from the track table by
ascending track length—that is, by the time column. Since it's likely that two or more
tracks have the same length, we'll add a second sort key to resolve collisions and de-
termine how such ties should be broken. In this case, when the track times are the same,
we'll sort the answers alphabetically by track_name . Here's what you type:
mysql> SELECT time, track_name FROM track ORDER BY time, track_name;
+------+------------------------------------------------------------+
| time | track_name |
+------+------------------------------------------------------------+
| 1.34 | Intermission By Alan Wise [Olympia, Paris 12/11/01] |
| 1.81 | In A Silent Way |
| 2.38 | Rip This Joint |
| 2.78 | Jangling Jack |
| 2.81 | Full Nelson |