T-SQL Subquery Max(Date) and Joins

Here's another way to do it without subqueries. This method will often outperform others, so it's worth testing both methods to see which gives the best performance.

SELECT     PRT.PartID,     PRT.PartNumber,     PRT.Description,     PRC1.Price,     PRC1.PriceDateFROM     MyParts PRTLEFT OUTER JOIN MyPrices PRC1 ON     PRC1.PartID = PRT.PartIDLEFT OUTER JOIN MyPrices PRC2 ON     PRC2.PartID = PRC1.PartID AND     PRC2.PriceDate > PRC1.PriceDateWHERE     PRC2.PartID IS NULL

This will give multiple results if you have two prices with the same EXACT PriceDate (Most other solutions will do the same). Also, I there is nothing to account for the last price date being in the future. You may want to consider a check for that regardless of which method you end up using.

Try this:

Select *,    Price = (Select top 1 Price              From MyPrices              where PartID = mp.PartID              order by PriceDate desc            )from MyParts mp

SELECT    MyParts.*,MyPriceDate.Price,MyPriceDate.PriceDate    FROM MyParts        INNER JOIN (SELECT Partid, MAX(PriceDate) AS MaxPriceDate FROM MyPrice GROUP BY Partid) dt ON MyParts.Partid = dt.Partid        INNER JOIN MyPrice ON dt.Partid = MyPrice.Partid AND MyPrice.PriceDate=dt.MaxPriceDate