Question 300823 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get row of data for MAX value over GROUP BY and JOINS

I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age.
Here’s how my tables are defined.
users
id | age | gender | occupation | zipcode
ratings
userid | movieid | rating | ts

What I’ve done so far

  1. Since the count wasn’t explicitly defined, I wrote a query to find it.
   SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid

This gave me the count for every movie for every age.

10  1   1
11  1   1
13  1   3
14  1   1
15  1   2
16  1   4
17  1   4
18  1   6
19  1   15
20  1   22
21  1   14
  1. To find the maximum for each age
    SELECT age, MAX(mcount) AS mc
    FROM (
        SELECT age, movieid, COUNT(*) AS mcount
        FROM ratings
        JOIN users ON id = userid
        GROUP BY age, movieid
    ) t1
    GROUP BY age

7   1
10  1
11  1
13  5
14  3
15  5
16  5
17  11
18  16
19  21
20  25
21  23

This gave me the age and the max count. However, I also want the corresponding movie ID and this where I have been stuck. My thought process is to join these results with the first table but it is not working. Are there any other alternatives I can try?
This is the query I used.


SELECT users.age, ratings.movieid, count(*) as mc2
FROM ratings JOIN users ON id = userid 
INNER JOIN
(
    SELECT age, MAX(mcount) AS mc
    FROM (
        SELECT age, movieid, COUNT(*) AS mcount
        FROM ratings
        JOIN users ON id = userid
        GROUP BY age, movieid
    ) t1
    GROUP BY age
)t2
ON t2.age = users.age
WHERE mc2=t2.mc
GROUP BY users.age, ratings.movieid;
Tags:

2

Answers


  1. 0

    You can do it like this:

    SELECT t.age, t.movieid, t.mcount
FROM (
   SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid
) t
LEFT JOIN (
   SELECT age, movieid, COUNT(*) AS mcount
   FROM ratings
   JOIN users ON id = userid
   GROUP BY age, movieid
) t2
ON t.age = t2.age AND
   t.movieid <> t2.movieid AND
   t.mcount < t2.mcount
WHERE t2.age IS NULL

    Explanation:

    • first subselect
      • joins ratings and users
      • on the id of users
      • groups by age and movieid
      • to find the groups
      • selects the fields you need
      • yields all groups
    • second subselect
      • is the same as the first
    • we LEFT JOIN the two groups by
      • matching age
      • different movieid (so we will have sensible count comparisons)
      • and the first mcount being smaller than the second
      • because we want to find items of the first group
      • which do not have higher count matches
      • so we search for higher count matches
    • and in the WHERE clause we exclude the scenario when we found higher count matches in the second group than in the first group

    Fiddle: http://sqlfiddle.com/#!9/f8defe/6

    Login or Signup to reply.
  2. 0

    It is 8.2.0 – vnk

    WITH cte AS (
        SELECT age, 
               movieid, 
               COUNT(*) AS mcount, 
               RANK() OVER (PARTITION BY age, ORDER BY COUNT(*) DESC) rnk
        FROM ratings
        JOIN users ON id = userid
        GROUP BY 1, 2
        )
SELECT age, 
       GROUP_CONCAT(movieid) movie_ids, 
       mcount
FROM cte
WHERE rnk = 1
GROUP BY 1, 3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search