I am quite certain we cannot use the LIMIT clause for what I want to do - so wanted to find if there are any other ways we can accomplish this.

I have a table which captures which user visited which store. Every time a user visits a store, a row is inserted into this table.

Some of the fields are

• shopping_id (primary key)
• store_id
• user_id

Now what I want is - for a given set of stores, find the top 5 users who have visited the store max number of times.

I can do this 1 store at a time as:

select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 60
group by user_id,store_id 
order by visits desc Limit 5

This will give me the 5 users who have visited store_id=60 the max times

What I want to do is provide a list of 10 store_ids and for each store fetch the 5 users who have visited that store max times

select store_id,user_id,count(1) as visits 
from shopping 
where store_id in  (60,61,62,63,64,65,66)
group by user_id,store_id 
order by visits desc Limit 5

This will not work as the Limit at the end will return only 5 rows rather than 5 rows for each store.

Any ideas on how I can achieve this. I can always write a loop and pass 1 store at a time but wanted to know if there is a better way