Sql-server – Best way to filter result set by secondary key

query-performancesql server

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

row_number() over (partition by SecondaryKey 
                   order by DateCreated desc) 

this will partition the data by SecondaryKey and then you will get the most recent data (ordered by DateCreated desc)

with CTE as (
 select [PrimaryKey], SecondaryKey, [Data],DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data],DateCreated
 From CTE where rn = 1 --rn = 1 is most recent data since we are ordering by desc

see SQLFiddle