Find last record by date - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Find last record by date

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-10-26 : 15:16:59

I have a product table that I'm joining to my ProductPrice table. The ProductPrice table contains multiple records for the same product number with different start dates and end dates. I would like to query to only show 1 record per product with the most current price. Any help is appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-26 : 15:43:28

There are a couple of ways to get the result you want. Two options are to use a CROSS APPLY or Ranking function. Here is one methos using a ranking function:

SELECT
    *
FROM
    (    
        SELECT
            *,
            ROW_NUMBER() OVER(PARTITION BY ProductNumber ORDER BY EndDate DESC) AS RowNum
        FROM
            Product
    ) AS T
WHERE
    T.RowNum = 1

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-10-26 : 15:49:55

Thanks - I think this may also work:
select * FROM sales_prices as t
where StartDate=(select max(StartDate) from sales_prices where [ProductID]=t.[ProductID])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources