-> ORDER BY played DESC LIMIT 5)

| Temptation |
| True Faith |
| The Perfect Kiss |
| Ceremony |
| New Blues |
| Intruder |
| In A Silent Way |
| Bizarre Love Triangle |
| Crystal |
+-----------------------+
10 rows inset sec)
The first query uses
ORDER
BY
with the
ASC
(ascending) modifier and a LIMIT clause to find the first five tracks played. The second query uses ORDER BY
with the
DESC
(de- scending) modifier and a LIMIT clause to find the last five tracks played. The
UNION
combines the result sets.
The
UNION
operator has several limitations The output is labeled with the names of the columns or expressions from the first query. Use column aliases to change this behavior The queries should output the same number of columns. If you try using different numbers of columns, MySQL will report an error All matching columns should have the same type. So, for example, if the first column output from the first query is a date, the first column output from any other query must be a date The results returned are unique, as if you’d applied a
DISTINCT
to the overall result set. To see this inaction, let’s add anew row for the track Fine Time to the played table. This has artist_id
1, album_id
3, and track_id
0:
mysql> INSERT INTO played SET
-> artist_id = 1,
-> album_id = 3,
-> track_id = 0,
-> played 10:27:03';
Query OK, 1 row affected (0.02 sec)
We’ve used the more verbose
INSERT
format to clarify what we’re inserting.
Now, if you run the previous
SELECT
query again, you’ll see 9 rows instead of since Fine Time appears twice in the first 5 tracks placed, but the implicit
DIS
TINCT
operation means it’s shown only once:
mysql> (SELECT track_name FROM

Download 4.24 Mb.

Share with your friends: