Sql Query - Limiting query results
- by Gublooo
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
