0

I have a query like this

SELECT m.title
FROM Movie AS M
WHERE
(SELECT MAX(r.stars)
 FROM rating AS r
WHERE r.mID = M.mID);

I want to show the MAX(r.stars) in my result set, what am I doing wrong?

2

I don't think you need a sub query here. Does this give you what you need? If so it should be more efficient.

SELECT m.title, MAX(r.stars)
FROM Movie AS m
LEFT OUTER JOIN rating AS r ON r.mID = m.mID;
GROUP BY m.title

Only way it could break that I can see is if you have two movies with the same title and different mID's.

  • Using GROUP BY m.mID, m.title would solve that. (not needed only if m.title has a unique constraint) – ypercubeᵀᴹ May 21 '14 at 12:21
  • of course. Good point. – James Anderson May 21 '14 at 13:16
0

Replace your WHERE with a LEFT OUTER JOIN, or INNER JOIN if you only want movies with ratings.

SELECT m.title, r.MaxRating
FROM Movie AS M
LEFT OUTER JOIN
(
    SELECT mID, MAX(r.stars) AS MaxRating
    FROM rating 
    GROUP BY mID
)AS r ON r.mID = M.mID;

Not the answer you're looking for? Browse other questions tagged or ask your own question.