Finding the latest record in each window – MariaDB/MySQL

Issue

In MariaDb 10.3, how to find the latest(based on timestamp) row for each window(or partition, I am not entirely clear on the terminology here)?

Consider the following table with data

ItemID Itemname Value Timestamp
1 A 22 2021-12-22 20:01:00
1 A 2 2021-12-22 15:09:44
1 A 3 2021-12-22 14:39:49
2 B 54 2021-12-22 12:46:37
2 B 23 2021-12-22 12:17:52
2 B 43 2021-12-22 11:19:11
1 A 23 2021-12-22 04:00:58
1 A 53 2021-12-22 03:00:58
3 C 21 2021-12-21 04:00:58
2 B 74 2021-12-21 04:06:58
2 B 36 2021-12-21 04:06:09
1 A 34 2021-12-21 03:08:09

Desired output

ItemID ItemName Value Timestamp
1 A 22 2021-12-22 20:01:00
2 B 54 2021-12-22 12:46:37
1 A 23 2021-12-22 04:00:58
3 C 21 2021-12-21 04:00:58
2 B 74 2021-12-21 04:06:58
1 A 34 2021-12-21 03:08:09

Solution

Following query generates expected result

WITH ordered AS (
  SELECT 
    *, 
    LAG(`ItemID`) OVER (ORDER BY `Timestamp` DESC) AS LastItem
  FROM dataset
)
SELECT `ItemID`, `ItemName`, `Value`, `Timestamp`
FROM ordered
WHERE `ItemID` <> `LastItem` OR `LastItem` IS NULL
ORDER BY `Timestamp` DESC

demo

Answered By – ProGu

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published