MySQL: Limit number of rows returned with a given column value
1
vote
0
answers
797
views
My table has a list of activities, each associated with a customer and each with a date when the activity happened. I want to group by the customer ID and the month and the year, then sort by the month, the year and the count to generate a list of time activity generators by month.
SELECT
original_actor_id
,
MONTH(activities.created_at
) as month,
YEAR(activities.created_at
) as year,
COUNT(*) as count
FROM activities
activities
WHERE activity_type
= 'product_video_completed'
GROUP BY
original_actor_id
,
MONTH(activities.created_at
),
YEAR(activities.created_at
)
ORDER BY
YEAR(activities.created_at
) DESC,
MONTH(activities.created_at
) DESC,
count DESC
However, I am only interested in the top 5 counts for each month/year combo. What is the best way to limit this to the top 5 higest counts for each month/year combo?
Asked by user41527
(111 rep)
Oct 31, 2017, 09:27 PM
Last activity: Aug 8, 2018, 08:01 PM
Last activity: Aug 8, 2018, 08:01 PM