Sql query - selecting top 5 rows and further selecting rows only if User is present
- by Gublooo
Hello,
I kind of stuck on how to implement this query - this is pretty similar to the query I posted earlier but I'm not able to crack it.
I have a shopping table where everytime a user buys anything, a record is inserted.
Some of the fields are
* shopping_id (primary key)
* store_id
* user_id
Now what I need is to pull only the list of those stores where he's among the top 5 visitors:
When I break it down - this is what I want to accomplish:
* Find all stores where this UserA has visited
* For each of these stores - see who the top 5 visitors are.
* Select the store only if UserA is among the top 5 visitors.
The corresponding queries would be:
select store_id from shopping where user_id = xxx
select user_id,count(*) as 'visits' from shopping
where store_id in (select store_id from shopping where user_id = xxx)
group by user_id
order by visits desc
limit 5
Now I need to check in this resultset if UserA is present and select that store only if he's present.
For example if he has visited a store 5 times - but if there are 5 or more people who have visited that store more than 5 times - then that store should not be selected.
So I'm kind of lost here.
Thanks for your help
