Consulting register with Mysql-CodePudding

How do i get the date of the highest minutes for each user in the table?

I'm having trouble trying to resolve this question, does anyone have any on how to solve this problem.

INSERT INTO watched_time (id,user_id, channel_id,minutes,`date`)
VALUES
(1,1,1,100.0,'2021-01-01 00:00:00.0'),
(2,1,1,180.0,'2021-01-02 00:00:00.0'),
(3,1,1,150.0,'2021-01-03 00:00:00.0'),
(4,1,1,110.0,'2021-01-04 00:00:00.0'),
(5,2,1,110.0,'2021-01-04 00:00:00.0'),
(6,2,1,140.0,'2021-01-05 00:00:00.0'),
(7,2,1,190.0,'2021-01-06 00:00:00.0'),
(8,3,1,170.0,'2021-01-01 00:00:00.0'),
(9,3,1,120.0,'2021-01-02 00:00:00.0'),
(10,3,1,130.0,'2021-01-03 00:00:00.0'),
(11,1,2,130.0,'2021-01-03 00:00:00.0'),
(12,2,2,130.0,'2021-01-03 00:00:00.0'),
(13,3,2,125.0,'2021-01-03 00:00:00.0'),
(14,1,2,110.0,'2021-01-05 00:00:00.0'),
(15,1,2,100.0,'2021-01-01 00:00:00.0'),
(16,2,2,120.0,'2021-01-01 00:00:00.0'),
(17,3,2,120.0,'2021-01-01 00:00:00.0');

CodePudding user response：

Use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY minutes DESC) rn
    FROM yourTable
)

SELECT id, user_id, channel_id, minutes, date
FROM cte
WHERE rn = 1;