CODEWITHSUNDEEP
mysqldatetimegroup-by
user1578872
user1578872

Reputation: 9018

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

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).

Upvotes: 0

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

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;

Upvotes: 3

Related Questions