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.

Solution 1

Try this:

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

Solution 2

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.PriceDate
FROM
     MyParts PRT
LEFT OUTER JOIN MyPrices PRC1 ON
     PRC1.PartID = PRT.PartID
LEFT OUTER JOIN MyPrices PRC2 ON
     PRC2.PartID = PRC1.PartID AND
     PRC2.PriceDate > PRC1.PriceDate
WHERE
     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.

Solution 3

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

Solution 4

In SQL Server 2005 and later use ROW_NUMBER():

SELECT * FROM 
    ( SELECT p.*,
        ROW_NUMBER() OVER(PARTITION BY Partid ORDER BY PriceDate DESC) AS rn
    FROM MyPrice AS p ) AS t
WHERE rn=1

Solution 5

Something like this

SELECT * 
FROM MyParts 
LEFT JOIN 
(
SELECT MAX(PriceDate), PartID FROM MyPrice group by PartID
) myprice
 ON MyParts.Partid = MyPrice.Partid 

If you know your partid or can restrict it put it inside the join.

   SELECT myprice.partid, myprice.partdate, myprice2.Price, * 
    FROM MyParts 
    LEFT JOIN 
    (
    SELECT MAX(PriceDate), PartID FROM MyPrice group by PartID
    ) myprice
     ON MyParts.Partid = MyPrice.Partid 
    Inner Join MyPrice myprice2
    on myprice2.pricedate = myprice.pricedate
    and myprice2.partid = myprice.partid

View more solutions