Advanced Querying - Learning MySQL - page 244

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 in set (0.00 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 in set (0.00 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 (see the second and third