CODEWITHSUNDEEP
sql
batsta13
batsta13

Reputation: 539

trouble query finding the entry with the highest number

I need some help a query. For each movie in my database that has at least one rating, I need to find the highest number of stars that movie has recieved. The data I want the query to return is movie, title and the number of stars sorted by movie title.

The tables I am working with are the following:

Movie(mID int, title text, year int, director text);
Reviewer(rID int, name text);
Rating(rID int, mID int, stars int, ratingDate date);

Upvotes: 1

Views: 214

Answers (2)

MatBailie
MatBailie

Reputation: 86765

SELECT
  Movie.*,
  Rating.Stars
FROM
  Movie
INNER JOIN
  Rating
    ON Movie.mid = Rating.mid
WHERE
  Rating.Stars = (SELECT MAX(Stars) FROM Rating)

(Wow, so hard to do this on an iPhone.)

Upvotes: 1

arturro
arturro

Reputation: 1606

Here you are:

select m.title, max(stars) 
from movie m join rating r on (m.mid = r.mid)
group by m.mid, m.title
order by m.title;

Upvotes: 1

Related Questions