T-SQL query help - select unique grouping?

Question

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

Answer 1

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

So for each RecordId, the entry with RowNumber = 1 is the most recent one - that's what the second (outer) SELECT is doing.

Answer 2

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

Answer 3

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