Nesting MAX for SELECT

aggregatesqlite

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 2×03 from series 1, and 2×05 from series 3.

I have 3 ideas, none of them great:

  1. Create a new integer column, calculated as season x 100 + episode, now I can sort easily by it, and quite fast, but I have to recalculate it every time if the season or episode column is updated.

  2. 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.

  3. 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?

Best Answer

It can be done with a query:

SELECT 
    s.*
FROM
  ( SELECT DISTINCT series_id
    FROM series
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season =
          ( SELECT si.season
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC
            LIMIT 1
          ) 
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC, si.episode DESC
            LIMIT 1
          ) ;

Tested at SQL-Fiddle

With an index on (series_id, season, episode), it should be quite efficient, if the number of series_id is small.

CREATE UNIQUE INDEX uix
  ON series
  (series_id, season, episode) ;

There are several ways to do this query but all get rather complicated because the maximum needed is based on two columns (season and episode). Here's another one, which is the query above, simplified a bit:

SELECT 
    s.*
FROM
  ( SELECT series_id, MAX(season) AS season
    FROM series
    GROUP BY series_id
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season = ds.season
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
              AND si.season = ds.season
            ORDER BY si.episode DESC
            LIMIT 1
          ) ;

and another using NOT EXISTS:

SELECT s.*
FROM series AS s
WHERE NOT EXISTS
      ( SELECT 1
        FROM series AS sb
        WHERE sb.series_id = s.series_id
          AND ( sb.season > s.season
             OR sb.season = s.season AND sb.episode > s.episode
              )
      ) ;
Related Question