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: 330

Filed under:
|

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

Related posts about sql

Related posts about mysql