T-SQL Subquery Max(Date) and Joins

Question

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date.

Here are some example tables:

Table: MyParts
Partid   Partnumber   Description
1        ABC-123      Pipe
2        ABC-124      Handle
3        ABC-125      Light


Table: MyPrices
Partid   Price        PriceDate
1        $1           1/1/2005
1        $2           1/1/2007
1        $3           1/1/2009
2        $2           1/1/2005
2        $4           1/1/2006
2        $5           1/1/2008
3        $10          1/1/2008
3        $12          1/1/2009

If I was just wanted to find the most recent price for a certain part I could do:

SELECT * FROM MyPrice WHERE PriceDate = (SELECT MAX(PriceDate) 
FROM MyPrice WHERE Partid = 1)

However I want to do a join first and get back the correct price for all parts not just one. This is what I have tried:

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE 
MyPart.PriceDate = (SELECT MAX(PriceDate) FROM MyPrice)

The results are wrong as it takes the highest price date of the entire table.

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE 
MyPart.PriceDate = (SELECT MAX(PriceDate) FROM MyPrice WHERE MyPrice.Partid =   
MyParts.Partid)

That errors out.

What can I do to get the results I want.

Answer 1

Try this:

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