CODEWITHSUNDEEP
sqlsql-server
Adrian Wragg
Adrian Wragg

Reputation: 7401

Returning a set of the most recent rows from a table

I'm trying to retrieve the latest set of rows from a source table containing a foreign key, a date and other fields present. A sample set of data could be:

create table #tmp (primaryId int, foreignKeyId int, startDate datetime, 
                                                    otherfield varchar(50))

insert into #tmp values (1, 1, '1 jan 2010', 'test 1')
insert into #tmp values (2, 1, '1 jan 2011', 'test 2')
insert into #tmp values (3, 2, '1 jan 2013', 'test 3')
insert into #tmp values (4, 2, '1 jan 2012', 'test 4')

The form of data that I'm hoping to retrieve is:

foreignKeyId maxStartDate            otherfield
------------ ----------------------- -------------------------------------------
1            2011-01-01 00:00:00.000 test 2
2            2013-01-01 00:00:00.000 test 3

That is, just one row per foreignKeyId showing the latest start date and associated other fields - the primaryId is irrelevant.

I've managed to come up with:

select t.foreignKeyId, t.startDate, t.otherField from #tmp t
    inner join (
                  select foreignKeyId, max(startDate) as maxStartDate
                      from #tmp
                      group by foreignKeyId
               ) s
           on t.foreignKeyId = s.foreignKeyId and s.maxStartDate = t.startDate

but (a) this uses inner queries, which I suspect may lead to performance issues, and (b) it gives repeated rows if two rows in the original table have the same foreignKeyId and startDate.

Is there a query that will return just the first match for each foreign key and start date?

Upvotes: 1

Views: 94

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

If you wanted to fix your attempt as opposed to re-engineering it then

select t.foreignKeyId, t.startDate, t.otherField from #tmp t
inner join (
  select foreignKeyId, max(startDate) as maxStartDate, max(PrimaryId) as Latest
  from #tmp
  group by foreignKeyId
           ) s
on t.primaryId = s.latest

would have done the job, assuming PrimaryID increases over time.

Qualms about inner query would have been laid to rest as well assuming some indexes.

Upvotes: 1

Codism
Codism

Reputation: 6224

Depending on your sql server version, try the following:

select *
from (
    select *, rnum = ROW_NUMBER() over (
      partition by #tmp.foreignKeyId
      order by #tmp.startDate desc)
    from #tmp
) t
where t.rnum = 1

Upvotes: 2

Related Questions