samedi 27 juin 2015

Selecting max values of a column using the max value of another column

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