Mysql group by date part of datetime and select rows with max datetime for each date-CodePudding

My data looks like,

Table - usr_weight

user_id	weight	log_time
1.	10	2021-11-30 10:29:03
1.	12	2021-11-30 12:29:03
1.	11	2021-11-30 14:29:03
1.	18	2021-12-01 08:29:03
1.	12	2021-12-15 13:29:03
1.	14	2021-12-15 17:29:03

Here, I have duplicates for each date with different time. So, group date and return the record with max time for each date.

Query

select weight, log_time from usr_weight where user_id = 1 group by DATE(log_time)

Here, I get 1 record for each date, but the row is not by max(log_time).

CodePudding user response：

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(log_time)
                                 ORDER BY log_time DESC) rn
    FROM usr_weight
    WHERE user_id = 1
)

SELECT user_id, weight, log_time
FROM cte
WHERE rn = 1;

Here is an old school join way of doing this:

SELECT uw1.user_id, uw1.weight, uw1.log_time
FROM usr_weight uw1
INNER JOIN
(
    SELECT DATE(log_time) AS log_time_date, MAX(log_time) AS max_log_time
    FROM usr_weight
    WHERE user_id = 1
    GROUP BY DATE(log_time)
) uw2
     ON uw2.log_time_date = DATE(uw1.log_time) AND
        uw2.max_log_time = uw1.log_time
WHERE
    uw1.user_id = 1;