0

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

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

3
  • Thanks, that does exactly what i'm looking for, but can i ask a follow up? Would it be more performant if i had multiple columns in the 'entry' table to include them in the CTE, and select them straight from the CTE, or to include as little data in the CTE as possible (just the 2 keys and the date), and then do a select data1, data2,.... from Entry Where primarykey in (select primarykey from CTE where rn=1) ?
    – Phil
    Mar 17 '20 at 18:08
  • If I understand your question - CTE is just a temp view (in layman's term) and whatever columns you pass in it, they will show up in CTE.
    – Kin Shah
    Mar 17 '20 at 18:31
  • see my update for a better phrasing of the question
    – Phil
    Mar 17 '20 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.