MySQL – Joins and Subqueries [closed]

I’m looking a question that asks ‘List for each year the name of the worst film(s) released that year. Films are rated by score: the lower the score, the worse the film. In the event of a tie, all tied films should be listed. The results should appear chronologically.’

The tables are:

movies(id, title, yr, score, votes, director)
castings(movieid, actorid)
actors(id, name)

So far I’ve gotten:

SELECT m.title
FROM movies AS m
WHERE m.score = (SELECT MIN(score) FROM movies)
ORDER BY m.yr DESC

Answer

You are almost there. As it is, your query gives you the movie with the worst score overall all years. You need to correlate the subquery with the outer query:

select yr, title
from movies m
where score = (select min(score) from movies m1 where m1.year = m.year)
order by yr desc

You can get the same results with by joining the table with an aggregate subquery:

select m.yr, m.title
from movies m
inner join (select yr, min(score) score from movies group by yr) x
    on x.yr = m.yr and x.score = m.score