Select WHERE IN and LIMIT by number of rows

Question

I have a table which looks like this

 userid  |  value   | time
    
    1        0          1
    1        1          2
    1        2          3
    3        5          4
    4        6          5
    1        9          6
    3        10         7

Using a select where in query I would want to select userd, value, and time but limit the total number of rows pulled for each userid

My SELECT query,

select userid, value, time from table where userid in (1,3) order by time desc;

This query outputs all the values like so

 userid  |  value   | time
    
    1        0          1
    1        1          2
    1        2          3
    3        5          4
    3        10         7

I would hover want to limit the number of rows for each userid to two to get an output like so

   userid  |  value   | time
    
    1        0          1
    1        1          2
    3        5          4
    3        10         7

I tried using limit but that limits the number of rows for the entire output.

Answer 1

You can use a rank query to limit rows per user

select userid, 
value,
`time`
from (
select *,
@r:= case when @g = userid then @r + 1 else 1 end row_num,
@g:=userid
from test t
cross join (select @g:= null,@r:=0) t1
where userid in (1,3)
order by userid,value,`time` desc
) t2
where row_num <= 2

Above query will give rank to each record for same user like user 1 has 3 records the they will assigned rank as 1,2,3 and if user 2 has 2 records then rank will be 1,2 for user 2 and in parent query i am just filtering the records according to the rank that return only result where rank is less than equal to 2 for for each user only 2 rows with rank 1 and 2 will be returned

Demo