Select the last number of records of all users from the table

Question

I have a table below and you want to select only the last 2 entries of all users.

Source table:

-------------------------------------
UserId | QuizId(AID)|quizendtime(AID)|
--------------------------------------
1         10          2016-5-12
2         10          2016-5-12 
1         11          2016-6-12
2         12          2016-8-12
3         12          2016-8-12
2         13          2016-8-12
1         14          2016-9-12
3         14          2016-9-12
3         11          2016-6-12

The expected result is similar to (should only display the last 2 quizid entries for all users)

-------------------------------------
UserId | QuizId(AID)|quizendtime(AID)|
--------------------------------------
1         14          2016-9-12
1         11          2016-6-12
2         13          2016-8-12
2         12          2016-8-12
3         14          2016-9-12
3         12          2016-8-12

Any idea to make this conclusion.

Answer 1

Using variables MySQL user defined, you can do the following:

SELECT 
t.UserId,
t.`QuizId(AID)`,
t.`quizendtime(AID)`
FROM 
(
    SELECT 
    *,
    IF(@sameUser = UserId, @a := @a + 1 , @a := 1) row_number,
    @sameUser := UserId
    FROM your_table
    CROSS JOIN (SELECT @a := 1, @sameUser := 0) var
    ORDER BY UserId , `quizendtime(AID)` DESC
) AS t
WHERE t.row_number <= 2

Working demo

Note. If you want no more than the xnumber of entries for each user, change the condition in the where section, as shown below:

WHERE t.row_number <= x

Explanation:

SELECT 
 *,
 IF(@sameUser = UserId, @a := @a + 1 , @a := 1) row_number,
 @sameUser := UserId
FROM your_table
CROSS JOIN (SELECT @a := 1, @sameUser := 0) var
ORDER BY UserId , `quizendtime(AID)` DESC;

userId quizendtime(AID).

() .

, userId, row_number (1). , row_number.

, , row_number <= 2, .

EDIT:. , mysql , , , :/p >

SELECT 
t.UserId,
t.`QuizId(AID)`,
t.`quizendtime(AID)`
FROM 
(
    SELECT 
    *,
    IF (
            @sameUser = UserId,
            @a := @a + 1,
            IF(@sameUser := UserId, @a := 1, @a:= 1)
        )AS row_number
    FROM your_table
    CROSS JOIN (SELECT @a := 1, @sameUser := 0) var
    ORDER BY UserId , `quizendtime(AID)` DESC
) AS t
WHERE t.row_number <= 2;

V2

Answer 2

. . MySQL select - , , .

select t.*
from (select t.*,
             (@rn := if(@u = UserId, @rn + 1,
                        if(@u := UserId, 1, 1)
                       )
             ) as rn
       from t cross join
            (select @u := -1, @rn := 0) params
       order by UserId, quizendtime desc
      ) t
where rn <= 2;