Learning Mysql

rows, even though the first subquery does have a ORDER BY
clause. Since we’re performing a
UNION
operation, the MySQL server has decided that there’s no point sorting the result of the subquery. The second subquery includes a
LIMIT
operation,
so the results of that subquery are sorted.
The output of a
UNION
operation isn’t guaranteed to be ordered, even if the subqu- eries are ordered, so if you want the final output to be ordered, you should add an
ORDER BY
clause at the end of the whole query:
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)
-> ORDER BY played;
+-----------------------+---------------------+
| 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 |
| Crystal | 2006-08-14 10:47:21 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| In A Silent Way | 2006-08-15 14:00:03 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Intruder | 2006-08-15 14:26:12 |
| Intruder | 2006-08-15 14:26:12 |
| New Blues | 2006-08-15 14:33:57 |
| New Blues | 2006-08-15 14:33:57 |
+-----------------------+---------------------+
17 rows inset sec)
Here’s another example of sorting the final results, including a limit on the number of returned results:
mysql> (SELECT artist_name FROM artist WHERE artist_id < 5)

Download 4.24 Mb.

Share with your friends: