Sql Query - Limiting query results

mysql, sql

Solution

Using two user variable and counting the same consecutive store_id, you can replace `<= 5` with whatever limit you want

SELECT a.*
FROM (
 SELECT store_id, user_id, count(1) as visits 
 FROM shopping
 WHERE store_id IN (60,61,62,63,64,65,66)
 GROUP BY store_id, user_id
 ORDER BY store_id, visits desc, user_id
) a,
(SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.store_id THEN
   CASE WHEN @prev:=a.store_id THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 5

Edit as requested some explanation :

The first subquery (a) is the one that group and order the data so you will have data like:

store_id | user_id | visits
---------+---------+-------
 60           1       5
 60           2       3
 60           3       1
 61           2       4
 61           3       2

the second subquery (b) init the user variable `@prev` with -1 and `@count` with 1

then we choose all data from the subquery (a) verifying the condition in the `case`.

verify that the previous store_id (`@prev`) we have seen is different from the current store_id. Since the first `@prev` is equal to -1 there is nothing that match the current store_id so the condition `<>` is true we enter then is the second case who just serve to change the value `@prev` with the current store_id. This is the trick so i can change the two user variable `@count` and `@prev` in the same condition.

if the previous store_id is equal to `@prev` just increment the `@count` variable.

we check that the count is within the value we want so the `<= 5`

So with our test data the:

step | @prev | @count | store_id | user_id | visits
-----+-------+--------+----------+---------+-------
  0      -1      1    
  1      60      1        60          1        5 
  2      60      2        60          2        3
  3      60      3        60          3        1
  4      61      1        61          2        4
  5      61      2        61          3        2

Problem

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

Original source