Finding max rating for a movie

I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.

The following is the question from the SQL Movie-Rating Query Exercises, Question 7:


For each movie that has at least one rating, find the highest number
of stars that movie received. Return the movie title and number of
stars. Sort by movie title.

The database can be found here.

My answer to this question is as follows:

SELECT distinct Movie.title, Rate.stars 
FROM Movie, Rating, 
(SELECT * FROM Rating R1 WHERE not exists (SELECT mID FROM Rating R2 WHERE R1.stars < R2.stars and R1.mID = R2.mID)) as Rate 
WHERE Movie.mID = Rate.mID and Rate.stars = Rating.stars
order by Movie.title;

This seems like a very tortured query, and it seems to me like I am missing some important concepts. Can someone help me refactor this query?

SELECT distinct Movie.title, Rate.stars

You should rarely use the DISTINCT keyword. In this particular case it's unnecessary and may do the wrong thing. What you want to do is to return the highest star rating for a given movie title:

SELECT Movie.title, MAX(Rating.stars)

There's the title and we use the MAX keyword to make sure that it's the highest stars. More on when we can use MAX later.

FROM Movie, Rating, 
(SELECT * FROM Rating R1 WHERE not exists (SELECT mID FROM Rating R2 WHERE R1.stars < R2.stars and R1.mID = R2.mID)) as Rate
WHERE Movie.mID = Rate.mID and Rate.stars = Rating.stars

We can make this simpler:

FROM Movie, Rating
WHERE Movie.mID = Rating.mID

No subselects required.

GROUP BY Movie.title
ORDER BY Movie.title;

The GROUP BY will allow us to use MAX. It says to return only one row per Movie.title value. The other columns need to be aggregated with grouping functions, like MAX. You already had the ORDER BY clause and presumably it's correct.

StackExchange Code Review Q#79256, answer score: 4