I have a snapshot table like the following

I would like to get the new rows only like the following table from the above snapshot table using sql

I need a pointer whether to follow the window function (like LAG() etc.) to get the new table. or more simple solution is there? Thanks in advance!

CodePudding user response：

There are a few options here, one of them is to use a cte or a derived table to add row_number based on the date column to the table, and the other is to use first_value window function. I'm pretty sure the derived table solution would be better in terms of performance, but I don't have the time to test. Here's what I would do:

;WITH cte AS
(
    SELECT id, name, value, date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY date DESC) as rn
    FROM snapshotTable
)
SELECT id, name, value, date
FROM cte
WHERE rn = 1;

To get the earliest records all you need to do is remove the DESC from the order by clause.