MaxGeek MaxGeek - 4 months ago 7
SQL Question

T-SQL Subquery Max(Date) and Joins

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.

wcm wcm
Answer

Try this:

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