user3110655 user3110655 - 3 months ago 9
MySQL Question

get last record of each user for each category

I want to get the last record of each user for each category.

e.g I have a table

tests (test_id,cat_id,user_id,score,time)

I need each user last record for each category. I can use group by either category_id or by user_id but I am not getting how to get my desired result?

e.g I have following records

test_id | cat_id | user_id | score | time
1 | 1 | 11 | 20 | 2016-11-12 01:11:11
2 | 2 | 11 | 24 | 2016-11-12 01:11:11
3 | 1 | 12 | 25 | 2016-11-12 01:11:11
4 | 3 | 12 | 21 | 2016-11-12 01:11:11
5 | 1 | 13 | 22 | 2016-11-12 01:11:11
6 | 2 | 12 | 23 | 2016-11-12 01:11:11
7 | 2 | 12 | 27 | 2016-11-12 01:11:11
8 | 1 | 11 | 21 | 2016-11-12 01:11:11


Now I need following result

test_id | cat_id | user_id | score | time
2 | 2 | 11 | 24 | 2016-11-12 01:11:11
3 | 1 | 12 | 25 | 2016-11-12 01:11:11
4 | 3 | 12 | 21 | 2016-11-12 01:11:11
5 | 1 | 13 | 22 | 2016-11-12 01:11:11
7 | 2 | 12 | 27 | 2016-11-12 01:11:11
8 | 1 | 11 | 21 | 2016-11-12 01:11:11


In above o/p each user's only last result is coming of each category.

Answer

Here we need a last user for all categories. SO we have to do group by category and find the user which have highest time for it.

SELECT t1.* 
FROM tests t1
INNER JOIN
(
    SELECT max(time) MaxTime,user_id,cat_id
    FROM tests
    GROUP BY user_id,cat_id
) t2
  ON t1.user_id = t2.user_id
  AND t1.cat_id = t2.cat_id
  AND t1.time = t2.MaxTime
order by t1.time desc
Comments