Selecting the Most Current Record in a table (SQL2000 and 2005 examples)
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
