MYSQL MIN in WHERE clause

Question

OK, so I'm working on a home theater system, and to display the list of TV series I have been selecting the first episode of the first season and displaying the associated information for just that episode, then you can drill down to others.

That way I can keep all the movies and all the TV shows in a single table which makes the playback functions much easier.

The problem is, some of the series I don't have the first episode in the database, and I want them to appear anyway, so I want to select the minimum season/series instead of the first.

I can't put MIN in the select clause as then it only selects one series, and I can't put LEAST in the WHERE clause because it says it's only one element because of the way they're grouped, and I can't use LIMIT because again then it will only select one series.

Is there a way to do this without multiple complicated subqueries?

Here's the query I'm using at the moment, the CASE clause is to remove A/An/The from the series title for proper alphabetical order:

SELECT *, CASE when substring(series,1,4) = 'The ' then substring(series, 5)
               when substring(series,1,3) = 'An ' then substring(series, 4)
               when substring(series,1,2) = 'A ' then substring(series, 3)
               else series END AS sorttitle 
FROM Theater.Videos WHERE type='TV' and season=1 and episode=1 ORDER BY sorttitle ASC

And here's essentially what I want:

SELECT *, CASE when substring(series,1,4) = 'The ' then substring(series, 5)
               when substring(series,1,3) = 'An ' then substring(series, 4)
               when substring(series,1,2) = 'A ' then substring(series, 3)
               else series END AS sorttitle 
FROM Theater.Videos WHERE type='TV' and season=MIN(season) and episode=MIN(episode) ORDER BY sorttitle ASC

Answer 1

Try these queries (after suitable modification)...

To get list of all series along with first episode of the first (min) season use this...

select distinct v1.*
from videos v1,
    (select series, min(season) as min_season from videos group by series) as v2,
    (select series, season, min(episode) as min_episode from videos group by series, season) as v3
where v1.series = v2.series
and v1.season = v2.min_season
and v2.series = v3.series
and v2.min_season = v3.season
and v1.episode = v3.min_episode

To get list of all series along with first episode of each season use this...

select distinct v1.*
from videos v1,
    (select series, season, min(episode) as min_episode from videos group by series, season) as v3
where v1.series = v3.series
and v1.season = v3.season
and v1.episode = v3.min_episode

Answer 2

its hard to say something without seen tha DB structure. If you can please put the sample overview of your db table.
or assign the where condition part in to a variable and first check that the series which do not have the first episode and get the results using while loop. after end of the while execute the next where condition according to your logic.

Answer 3

You can JOIN the table to itself using a subquery with MIN() -- this should be close:

SELECT V.*, CASE when substring(V.series,1,4) = 'The ' then substring(V.series, 5)
               when substring(V.series,1,3) = 'An ' then substring(V.series, 4)
               when substring(V.series,1,2) = 'A ' then substring(V.series, 3)
               else V.series END AS sorttitle 
FROM Theater.Videos V
    JOIN (
        SELECT series, Min(season) MinSeason, MIN(Episode) MinEpisode
        FROM Theater.Videos
        GROUP BY series
    ) t ON t.series = V.series 
        AND t.season = V.MinSeason 
        AND t.episode = V.MinEpisode
WHERE V.type='TV' 
ORDER BY V.sorttitle ASC