How To: Query: Get Unique and Latest Record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How To: Query: Get Unique and Latest Record

Author

Topic

Jawad Khan
Starting Member

21 Posts

Posted - 2008-10-17 : 02:19:58

Hi,

I have got two tables. Items And ItemsRateHistory
Items have two colums
ItemID and ItemName
ItemsRateHistory has three columns
ItemID, ChangeDate, NewRate

Here is some sample data in ItemsRateHistory

1------1-oct-08------$10
2------1-oct-08------$8
1------5-oct-08------$13
1------8-oct-08------$15

Now I want to query this table to get the latest rates of Items. The output should be:
1------8-oct-08------$15
2------1-oct-08------$8

Can any one help me writing the query?
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-17 : 02:38:08

select ItemId, ChangeDate, NewRate
from
(select
ItemId,
ChangeDate,
NewRate,
row_number() over (partition by ItemId order by ChangeDate desc) as num
from ItemsRateHistory)t
where num=1

Planning replaces chance by mistake

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-10-17 : 02:45:07

Declare @T table
(
id int,
date datetime,
price varchar(10)
)

Insert into @T

Select 1,getdate()-5,'$10' union all
Select 1,getdate()-2,'$1' union all
Select 2,getdate(),'$8' union all
Select 1,getdate(),'$15'

Select * from
(
select id,date, price, row_number() over( partition by id order by date) as
RID from @T
) T where RID=1

Jawad Khan
Starting Member

21 Posts

Posted - 2008-10-17 : 02:58:10

Thanks guys. That was quick. I was not familiar to the row_number() and partition.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-17 : 03:54:40