I have a table with a primary key and a secondary key, something like
CREATE TABLE Entry (
PrimaryKey int NOT NULL,
SecondaryKey int NOT NULL,
Data nvarchar(100) NOT NULL,
DateCreated DATETIME NOT NULL
);
INSERT INTO Entry VALUES(1,1,'test',getutcdate()),
(2,1,'test',getutcdate()),
(3,2,'test',getutcdate()),
(4,3,'test',getutcdate()),
(5,3,'test',getutcdate()),
(6,3,'test',getutcdate()),
(7,4,'test',getutcdate()),
(8,4,'test',getutcdate())
I want to be able to return the entries sorted by datecreated, but only 1 entry per secondary key. so with the above data, i would only get 4 rows back, the most recent entry for each secondary key.
Whats the best way to do this?
UPDATE:
question answered below, but the question i have next is 'consider the table if it has multiple data fields, what is a better query?' The original response, where everything is in the CTE:
with CTE as (
select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...DateCreated,
rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
from dbo.Entry
)
select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
From CTE where rn = 1
or in another query, where the data kept in the CTE is minimized:
with CTE as (
select [PrimaryKey], SecondaryKey, DateCreated,
rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
from dbo.Entry
)
SELECT PrimaryKey,SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
FROM ENTRY
WHERE PrimaryKey IN( select [PrimaryKey] FROM CTE where rn = 1 )
Best Answer
You should just use
this will partition the data by SecondaryKey and then you will get the most recent data (ordered by DateCreated desc)
see SQLFiddle