praba buddy praba buddy - 3 months ago 11
MySQL Question

Select recent n number of entries of all users from table

I have a below table and wants to select only 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


Expected output is like, (should list only recent 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's to produce this output.

Answer

Using MySQL user defined variables you can accomplish this:

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 at most x number of entries for each user then change the condition in where clause like 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;

This query sorts all the data in ascending order of userId and descending order of quizendtime(AID).

Now take a walk on this (multi) sorted data.

Every time you see a new userId assign a row_number (1). If you see the same user again then just increase the row_number.

Finally filtering only those records which are having row_number <= 2 ensures the at most two latest entries for each user.