Select Latest Price - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select Latest Price

Author

Topic

caisysro
Starting Member

2 Posts

Posted - 2009-08-15 : 17:28:21

Hi,
I have a product table:
product_id(int)
product_name(char)

and a price table:
product_id(int)
price(dec)
price_date(datetime)

The price table can contain more than one price for the same product.
How can i select all product from the product table and their most recent prices from the price table?
thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-15 : 17:37:02

select p.product_id, p.product_name, dt.price
from product p
join
(
select product_id, price, row_number() over (partition by product_id order by price_date DESC) as rownum from price)dt
on dt.product_id=p.product_id and rownum==1

No, you're never too old to Yak'n'Roll if you're too young to die.