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

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