Databases Reference
In-Depth Information
First, let's run a simple query to list the tracks that have been played, along with
the time each track was played. We've enclosed the query in parentheses for con-
sistency with our other examples—the parentheses don't actually have any effect
here—and haven't used an
ORDER BY
or
LIMIT
clause:
mysql>
(SELECT track_name, played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> );
+-----------------------+---------------------+
| track_name | played |
+-----------------------+---------------------+
| Fine Time | 2006-08-14 10:21:03 |
| Fine Time | 2006-08-14 10:27:03 |
| Temptation | 2006-08-14 10:25:22 |
| True Faith | 2006-08-14 10:30:25 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| Ceremony | 2006-08-14 10:41:43 |
| Regret | 2006-08-14 10:43:37 |
| Crystal | 2006-08-14 10:47:21 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Intruder | 2006-08-15 14:26:12 |
| New Blues | 2006-08-15 14:33:57 |
+-----------------------+---------------------+
12 rows in set (0.00 sec)
The query returns all the played tracks, in no particular order (see the second and
third entries).
Now, let's add an
ORDER BY
clause to this query:
mysql>
(SELECT track_name, played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played ASC);
+-----------------------+---------------------+
| track_name | played |
+-----------------------+---------------------+
| Fine Time | 2006-08-14 10:21:03 |
| Temptation | 2006-08-14 10:25:22 |
| Fine Time | 2006-08-14 10:27:03 |
| True Faith | 2006-08-14 10:30:25 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| Ceremony | 2006-08-14 10:41:43 |
| Regret | 2006-08-14 10:43:37 |
| Crystal | 2006-08-14 10:47:21 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Intruder | 2006-08-15 14:26:12 |
| New Blues | 2006-08-15 14:33:57 |
+-----------------------+---------------------+
12 rows in set (0.03 sec)
As expected, we get all the played tracks, in the order that they've been played.
