I need to write a SQL Query which generates the name of the most popular story for each user (according to total reading counts). Here is some sample data:
story_name | user | age | reading_counts -----------|-------|-----|--------------- story 1 | user1 | 4 | 12 story 2 | user2 | 6 | 14 story 4 | user1 | 4 | 15
This is what I have so far but I don't think it's correct:
Select *
From mytable
where (story_name,reading_counts)
IN (Select id, Max(reading_counts)
FROM mytable
Group BY user
)
reading_counts
for every user (Group By
with Max()
)user
and reading_counts
, to get the row corresponding to maximum reading_counts
for a user.Try the following query:
SELECT
t1.*
FROM mytable AS t1
JOIN
(
SELECT t2.user,
MAX(t2.reading_counts) AS max_count
FROM mytable AS t2
GROUP BY t2.user
) AS dt
ON dt.user = t1.user AND
dt.max_count = t1.reading_counts