Problem with a query
Hello everyone,
I have a problem with a query.
The tables in the database are the following :
-Movie ( mID, title, year, director )
English: There is a movie with ID number mID, a title, a release year, and a director.
-Reviewer ( rID, name )
English: The reviewer with ID number rID has a certain name.
-Rating ( rID, mID, stars, ratingDate )
English: The reviewer rID gave the movie mID a number of stars rating (1-5) on a certain ratingDate.
The question is the following :
For each director, return the director's name together with the title(s) of the movie(s) they directed that received the highest rating among all of their movies, and the value of that rating. Ignore movies whose director is NULL.
After some thinking, I have written this solution :
select M.director, M.title, MAX(Ra.stars)
from Rating Ra join Movie M on M.mID = Ra.mID
where M.director is not null
group by M.director;
I think that there is a problem with my solution because if there is several movies with the highest rating for that director, my query will not retrieve it.
For example, if a given director has two different movies with its highest rating, I will get only the first movie and not the second one.
If it is the case, can you help with this query please ?
If it is not, can you explain why I am wrong with that thinking ?
Thank you in advance for your help.
Nacer