SQLite Max of Count problem

Question

I have the following query:

SELECT M.movieId, COUNT (*) AS mcount 
FROM Movies M, Rentals R 
WHERE M.movieId = R.movieId 
GROUP BY M.movieId

I have a Movies DB and a Rentals DB, the resulting table currently shows the movie ID and how many times it has been checked out but i just can't figure out how to incorporate a MAX call on the mcount. Every time I try to do it I get a syntax error.

I want to be able to find the movie(s) that have been checked out the most.

Answer 1

You could just sort by the count column and limit the result to the number you want

SELECT M.movieId, COUNT(*) AS mcount 
FROM Movies M, Rentals R 
WHERE M.movieId = R.movieId 
GROUP BY M.movieId
ORDER BY 2 DESC
LIMIT 1

would give you the top one.