2

I have searched a lot of different posts about this and i didn't find something that works for me.

This Using LIMIT within GROUP BY to get N results per group? and http://www.sqlines.com/mysql/how-to/get_top_n_each_group didn't work for me.

I have a simple MySQL Table with the following columns

id , package, user_id, date

I want to perform a query in which i will get

X Rows / user_id WHERE date > Number Order By date ASC

In short, we want to perform a Group By user_id with LIMIT of X rows / group but have in mind the statement using the date column

Example Full Table:

id , package, user_id, date
1,  full, 1 , 1447003159
2,  full, 1 , 1447003055
3,  full, 1 , 1447002022
4,  full, 1 , 1447001013
5,  full, 1 , 1447000031
6,  mid,  2 , 1447003159
7,  mid,  2 , 1447003055
8,  mid,  2 , 1447002022
9,  mid,  2 , 1447001013
10, mid,  2 , 1447000031

From the above table we want to Select only 2 rows / user_id but where date >= 1447000031 (But make ORDER BY date ASC first)

Expected Output:

4,  full, 1 , 1447001013
3,  full, 1 , 1447002022
9,  mid,  2 , 1447001013
8,  mid,  2 , 1447002022
Community
  • 1
  • 1
OhGodWhy
  • 165
  • 1
  • 11

1 Answers1

5

E.g.:

SELECT x.*
  FROM my_table x 
  JOIN my_table y 
    ON y.id >= x.id 
   AND y.user_id = x.user_id 
 WHERE y.date > 1447000031 
 GROUP 
    BY x.id 
HAVING COUNT(*) <= 2;

or, faster, but more typing...

SELECT id
     , package
     , user_id
     , date 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev_user = user_id THEN @i:=@i+1 ELSE @i:=1 END rank
            , @prev_user := user_id 
         FROM my_table x
            , ( SELECT @prev_user = 0,@i:=1 ) vars 
        WHERE date > 1447000031
        ORDER 
           BY user_id
        , date
 ) a 
 WHERE rank <= 2;
Strawberry
  • 33,750
  • 13
  • 40
  • 57