My table has 3 columns:
RecordId Value InsertDate
Each RecordId has multiple entries in the table. In fact the table gets updated several times a day.
How do I write a t-sql query to select all the latest rows (based on InsertDate) for each unique record?
My t-sql skills are non-existent.
Thanks in advance
You can use a CTE (Common Table Expression) and the ranking function – something like this:
;WITH YourQuery AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY RecordId ORDER BY InsertDate DESC) 'RowNumber' FROM dbo.YourTable WHERE InsertDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) ) SELECT * FROM YourQuery WHERE RowNumber = 1
The CTE (the inner SELECT) selects all rows, partitions them by
RecordId, orders them by
InsertDate descending (newest first) – so this gives you a list of all records, ordered by ID, InsertDate, and a
RowNumber field which starts at 1 for each new
So for each
RecordId, the entry with
RowNumber = 1 is the most recent one – that’s what the second (outer) SELECT is doing.
See @marc_s’s answer, but also you should definitely add another column to the table that is a unique id for the row. Not so much for this problem, but for later ones you’ll encounter. Even if you don’t think you’ll use it, there are lots of good reasons to have a primary key on the table.
alter table YourTable add Id int identity not null alter table YourTable add constraint "YourTable_PK" primary key ("Id")
I think it’s possible (and easier) without CTE and ROW_NUMBER…or am I missing something?
select RecordID, max(InsertDate) from YourTable group by RecordID