Learning Mysql

Adding a
LIMIT
clause to the previous query selects the first five tracks played, in chronological order—no surprises here:
mysql> (SELECT track_name, played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played ASC LIMIT 5);
+------------------+---------------------+
| 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 |
+------------------+---------------------+
5 rows inset sec)
Now, let’s see what happens when we perform a
UNION
operation. In this example,
we’re using two subqueries, each with an ORDER BY
clause. We’ve used a
LIMIT
clause for the second subquery, but not for the first:
mysql> (SELECT track_name, played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played ASC)
-> UNION ALL
-> (SELECT track_name,played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played DESC LIMIT 5);
+-----------------------+---------------------+
| 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 |
| New Blues | 2006-08-15 14:33:57 |
| Intruder | 2006-08-15 14:26:12 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| Crystal | 2006-08-14 10:47:21 |
+-----------------------+---------------------+
17 rows inset sec)
As expected, the first subquery returns all the played tracks (the first 12 rows of this output, and the second subquery returns the last 5 tracks (the last 5 rows of this output. Notice how the first 12 rows are not in order (seethe second and third

Download 4.24 Mb.

Share with your friends: