Hi folks I'm writing a query in MYSQL and it has challenged me. Here is the situation;
Let's assume I have a table named 'status' which keeps data for a tv show like that;
+---------+---------+---------+---------+
| id | season | episode | channel |
+---------+---------+---------+---------+
| 1 | 2 | 10 | a |
| 1 | 3 | 2 | b |
| 1 | 2 | 9 | c |
| 1 | 3 | 1 | d |
| 1 | 3 | 2 | e |
+---------+---------+---------+---------+
I want to retrieve the rows which contains the last released episode of the last season. According to the table above, I expect a result like that
+---------+---------+---------+
| season | episode | channel |
+---------+---------+---------+
| 3 | 2 | b |
| 3 | 2 | e |
+---------+---------+---------+
The max value of the season column is 3. In this case, I have retrieved the rows that have the max value of the episode column where season is equal to 3. I have written a query and it gives the expected result, but I don't think that it is an appropriate query. How could I improve the query below? If needed, I can add extra information or give further examples.
SELECT season,
episode,
channel
FROM `status`
WHERE `tvseriesid` = 1
AND `season` = (SELECT Max(season) AS Son
FROM `status`
WHERE `tvseriesid` = 1)
AND `episode` = (SELECT Max(episode)
FROM `status`
WHERE `tvseriesid` = 1
AND `season` = (SELECT Max(season) AS Son
FROM `status`
WHERE `tvseriesid` = 1))
Aucun commentaire:
Enregistrer un commentaire