-> USING (artist_id, album_id)

| 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 inset 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 inset 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 practice, 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

Download 4.24 Mb.

Share with your friends: