Sql query - selecting top 5 rows and further selecting rows only if User is present
Posted
by Gublooo
on Stack Overflow
See other posts from Stack Overflow
or by Gublooo
Published on 2010-03-23T16:52:52Z
Indexed on
2010/03/23
16:53 UTC
Read the original article
Hit count: 329
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
© Stack Overflow or respective owner