I have a table, containing TV series episodes:
series: | series_id | season | episode | title | 1 | 1 | 18 | xxx | 1 | 2 | 3 | xxy | 3 | 1 | 13 | yyy | 3 | 2 | 5 | yyy
I want to get the latest episode from each show, so 2x03 from series 1, and 2x05 from series 3.
I have 3 ideas, none of them great:
Create a new integer column, calculated as
seasonx 100 +
episode, now I can sort easily by it, and quite fast, but I have to recalculate it every time if the
episodecolumn is updated.
Write a select that first finds the max(
season) for each series, and use it in a second select. Easier to maintain, but I was not able to write the right sql statement yet.
Use a calculated aggregare:
SELECT series_id, max( 100* season + episodes ) as latest FROM series GROUP BY series_id;
Which option is better? How should I write option 2?