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 |
CodePudding user response：
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