mysql Query to get top score records for each seprate user with per user limit 3-CodePudding

Actually, I want top score records for each separate user with per user limit of 3 (each user 3 top score records) from the table. Also, there is a scenario where one user can attempt the same assessment multiple times with different scores in that we want only top score 1 records from all same assessment attempts.

id  modules_completion_status_id    attempt_score   user_id assessment_id
1           78632                          50   343   1553
2           78631                          80   343   1782
3           78629                          92   369   1782
4           78628                          92   453   1781
5           78611                          60   661   3281
6           78611                          80   661   3281
7           78611                          50   661   3281
8           78611                          60   661   3281
9           78599                          98   1473  1097
10          78599                          97   1473  1097
11          78599                          95   1473  1097
12          78599                          99   1473  1097
13          78599                          95   1473  1097
14          67566                          93   1473  700
15          61529                          77   1473  684
16          59491                          70   1473  141
17          61529                          80   1473  684

Expected Output is : Output

id  modules_completion_status_id    attempt_score   user_id assessment_id
13          78599                          99   1473    1097
27          67566                          93   1473    700
4           78628                          92   453     1781
3           78629                          92   369     1782
30          61529                          80   1473    684
2           78631                          80   343     1782
8           78611                          80   661     3281
1           78632                          50   343     1553

I tried with this query:

select rn.attempt_id,rn.modules_completion_status_id,rn.user_id,rn.attempt_score from ( SELECT assessment_attempt_score.id as attempt_id ,user_id, MAX(attempt_score) as attempt_score , modules_completion_status_id, row_number() over (partition by assessment_attempt_score.id order by attempt_score) as row_n FROM assessment_attempt_score group by assessment_attempt_score.id,user_id order by attempt_score desc ) rn where row_n < 3 group by rn.modules_completion_status_id,user_id order by attempt_score desc;

CodePudding user response：

This is how you can do such a selection:

SELECT ...
FROM yourtable first_score
LEFT JOIN yourtable second_score
ON first_score.user_id = second_score.user_id AND
   (
       first_score.attempt_score < second_score.attempt_score OR
       (
           first_score.attempt_score = second_score.attempt_score AND
           first_score.id < second_score.id
       )
   )
LEFT JOIN yourtable third_score
ON second_score.user_id = third_score.user_id AND
   (
       second_score.attempt_score < third_score.attempt_score OR
       (
           second_score.attempt_score = third_score.attempt_score AND
           second_score.id < third_score.id
       )
   )
LEFT JOIN yourtable nonexistent
ON third_score.user_id = nonexistent.user_id AND
   nonexistent.id NOT IN (first_score.id, second_score.id, third_score.id) AND
   (
       nonexistent.attempt_score > third_score.attempt_score OR
       (
           nonexistent.attempt_score = third_score.attempt_score AND
           nonexistent.id < third_score.id
       )
   )
WHERE nonexistent.id IS NULL

Explanation:

we get first_score
`second_score
third_score
with the criteria that each score is either higher than the next, or, in case of a tie, it has a smaller id
and we get the hypothetical (in fact, nonexistent) next record that would be different from the first, the second and third, but better scoring than the third
and of course we create the user link in the hierarchy
the WHERE clause ensures that it's encluded that there would exist any record besides the first three where the user would score better than the third

No need to use group by for this purpose.

CodePudding user response：

Use ROW_NUMBER() function, once to get for each user_id and assessment_id the top score and again to get the top 3 scores:

WITH 
  cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, assessment_id ORDER BY attempt_score DESC) rn1
    FROM assessment_attempt_score
  ),
  cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY attempt_score DESC) rn2
    FROM cte1
    WHERE rn1 = 1
  )
SELECT id, modules_completion_status_id, attempt_score, user_id, assessment_id
FROM cte2
WHERE rn2 <= 3
ORDER BY user_id, attempt_score DESC;

See the demo.