Sql query - selecting top 5 rows and further selecting rows only if User is present

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

This should do it. It uses an intermediate VIEW to figure out how many times each user has shopped at each store. Also, it assumes you have a stores table somewhere with each store_id listed once. If that's not true, you can change SELECT store_id FROM stores to SELECT DISTINCT store_id FROM shopping for the same effect but slower results.

 CREATE VIEW shop_results (store_id, user_id, purchase_count) AS
     SELECT store_id, user_id, COUNT(*)
     FROM shopping GROUP BY store_id, user_id

 SELECT store_id FROM stores 
    WHERE 'UserA' IN 
      (SELECT user_id FROM shop_results 
       WHERE shop_results.store_id = stores.store_id 
       ORDER BY purchase_count DESC LIMIT 5)

You can combine these into a single query by placing the SELECT from the VIEW inside the sub-query, but I think it's easier to read this way and it may well be true that you want that aggregated information elsewhere in the system — more consistent to define it once in a view than repeat it in multiple queries.