1

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?

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.