Databases Reference
In-Depth Information
| Brotherhood | All Day Long |
| Brotherhood | Bizarre Love Triangle |
| Brotherhood | Way of Life |
| Brotherhood | Broken Promise |
| Brotherhood | As It Is When It Was |
| Brotherhood | Weirdo |
| Brotherhood | Paradise |
| Exile On Main Street | Rocks Off |
| Exile On Main Street | Rip This Joint |
| Exile On Main Street | Shake Your Hips |
| Exile On Main Street | Casino Boogie |
| Exile On Main Street | Tumbling Dice |
+----------------------+-----------------------+
15 rows in set (0.00 sec)
You can see that the
ORDER BY
clause sorts the albums and tracks in the required order,
and that it's listed last in the query after the join condition.
Let's try a different query. Suppose you want to find out which tracks you've played.
You can do this with a join between the
track
and
played
tables, using the
artist_id
,
album_id
, and
track_id
columns in the join condition. Here's the query:
mysql>
SELECT played, track_name FROM
-> track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY track.artist_id, track.album_id, track.track_id, played;
+---------------------+-----------------------+
| played | track_name |
+---------------------+-----------------------+
| 2006-08-14 10:21:03 | Fine Time |
| 2006-08-14 10:25:22 | Temptation |
| 2006-08-14 10:30:25 | True Faith |
| 2006-08-14 10:36:54 | The Perfect Kiss |
| 2006-08-14 10:41:43 | Ceremony |
| 2006-08-14 10:43:37 | Regret |
| 2006-08-14 10:47:21 | Crystal |
| 2006-08-14 10:54:02 | Bizarre Love Triangle |
| 2006-08-15 14:00:03 | In A Silent Way |
| 2006-08-15 14:26:12 | Intruder |
| 2006-08-15 14:33:57 | New Blues |
+---------------------+-----------------------+
11 rows in set (0.00 sec)
We've sorted the results by artist, then album, then track, and then the play date and
time. Notice we've also had to unambiguously specify the columns in the
ORDER BY
clause using the table name, since the first three columns occur in both tables. In prac-
tice, if columns are used in the join condition, it doesn't matter whether you sort or
select using the column from either table; for example, in this query,
track.artist_id
and
played.artist_id
are interchangeable because they're always the same for each
row.
Before we leave
SELECT
, we'll give you a taste of one of the functions you can use to
aggregate
values. Suppose you want to find out how long New Order's
Brotherhood
