Freigeben über

Selecting the Most Current Record in a table (SQL2000 and 2005 examples)

  • Artikel

My friend Alfredo helped with some SQL code and I thought it would be interesting to share.   I was trying to select the most current record based on a unique id and a date within a single table.   I was familar with the SQL 2000 way of doing things (a sub select) but he helped me with some CTE and WITH functions new with SQL 2005. 

The scenario is I have a table with a unique id, interest rate and rate date (effective date).  I  need to return the latest record for each effective date.

Here is some sample code to evaluate, hope it helps!

Sample Code

if

object_id('dbo.t') is not null
   drop table dbo.t;
go

create

table t(
  UniqueID int not null,
  InterestRate int not null,
  RateDate datetime not null);
go

truncate

table dbo.t

insert

into dbo.t
  select 1,1,'6/1/2007'
union
  select 1,2,'6/2/2007'
union
  select 1,3,'6/3/2007'
union
  select 2,4,'7/1/2007'
union
  select 2,5,'8/1/2007'
union
  select 2,6,'9/1/2007'

select

* from dbo.t
go

--sql 2005 ver 1

select

t.*
from
(
select
  UniqueID
  , InterestRate
  , RateDate
  ,row_number() over(partition by UniqueID order by UniqueID,RateDate desc) as rn
from dbo.t
) as t
where rn=1
GO

--sql 2005 ver2

with t as
(
select
  UniqueID
  , InterestRate
  , RateDate
  ,row_number() over(partition by UniqueID order by UniqueID,RateDate desc) as rn
from dbo.t
)

select

* from t where t.rn=1
GO

--sql 2000

select
  t1.*
from dbo.t t1
inner join
(
select
  UniqueID,max(RateDate) as RateDate
from dbo.t
group by UniqueID
) t2
on t1.UniqueID = t2.UniqueID
and t1.RateDate = t2.RateDate

Comments