CODEWITHSUNDEEP
mysqlsql
agarwal_achhnera
agarwal_achhnera

Reputation: 2456

how to fetch max rating MovieId voted by max number of users

Hi I have a table named rating with below structure

userId int,movieId int,rating number,time varchar

I ave to fetch movieId with max rating voted by max users, for exp

userId  movieId  rating time
0001,    3,       5.0, 2312345

0001,    5,       3.0, 2312323

0001,    7,       4.0, 2312124

0002,    2,       3.0, 2312453

0002,    3,       5.0, 2312323

0002,    7,       3.0, 2315642

0003,    3,       5.0, 2312764

0004,    3,       4.0, 2312146

0004,    7,       3.0, 2312321

in this movieId 3 is voted 5.0 by 3 users, which is max rating with max number of user.

How can I do this with sql query

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

If you want one movie, do an aggregation and use order by and limit:

select movieId, rating, count(distinct userId) as NumUsers
from rating
group by movieId, rating
order by rating desc, NumUsers desc
limit 1;

Upvotes: 2

saif
saif

Reputation: 400

How about using this query:

SELECT movieId, rating, count(distinct userId) votecount
from rating
group by movieId, rating

here, who ever got the highest votecount, wins.

Upvotes: 0

Related Questions