views:

5045

answers:

8

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.

+3  A: 

try this:

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
KM
And the Price of the part for the latest date?
gbn
@gbn, edit will show that now
KM
+1  A: 
SELECT
    *
FROM
    (SELECT MAX(PriceDate) AS MaxP, Partid FROM MyPrices GROUP BY Partid) MaxP 
    JOIN
    MyPrices MP On MaxP.Partid = MP.Partid AND MaxP.MaxP = MP.PriceDate
    JOIN
    MyParts P ON MP.Partid = P.Partid

You to get the latest pricedate for partid first (a standard aggregate), then join it back to get the prices (which can't be in the aggregate), followed by getting the part details.

gbn
Will "select *" work if you have three columns named PartId?
Andomar
It will work if you simply copy and paste it into a query window and execute it.
wcm
@Andomar: yes. You'll just have 3 Partid columns in the output. Now we've given the FROM clause for the JOIN, OP can work out what columns hewants
gbn
+4  A: 

Try this:

Select *,
    Price = (Select top 1 Price 
             From MyPrices 
             where PartID = mp.PartID 
             order by PriceDate desc
            )
from MyParts mp
wcm
+1 Looks like the simplest solution to me
Andomar
Since it's for SQL 2005, I was going to do something fancy with the WITH statement then I just decided to go for the absolute simplest approach.
wcm
Maybe edit your answer so that it's readable without the scroll bar :P
Andomar
when I tried this on my system using (using two of my own tables with data in them) with "set showplan_all on". The traditional join syntax like what I use in my answer had a TotalSubtreeCost of 18.86766, while this syntax had a value of 35.66925. The result set was over 150k rows.
KM
@Andomar, good suggestion. Done
wcm
@KM, I don't know how your tables are indexed or anything else about your data but yours is probably better. I just wrote mine off the top of my head. @MaxGeek, you should probably use his. In my defense, mine worked correctly from the get go :)
wcm
Depending on the number of rows in MyParts, this is a subquery that will have to be evaluated once per row returned from the MyParts table. This could get very costly as its comparable to using a cursor.
Craig Nicholson
@Craig Nicholson: the optimizer will execute this as a left outer join, check the query plan. For really large amounts of data, check Tom H's answer, which can have better performance because it doesn't use top 1 or aggregates.
Andomar
I like Tom H's answer. It has a certain simple elegance. This just goes to show that you shouldn't necessarily just bang out the first piece of SQL that pop's into you head.
wcm
I have a need for a query similar to this in my application. This solution was much faster than mine. Thanks! (wcm is too modest)
ep4169
+2  A: 

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
u07ch
Where is latest price for the partid in the output?
gbn
oops - updated to include price
u07ch
+1  A: 

Join on the prices table, and then select the entry for the last day:

select pa.partid, pa.Partnumber, max(pr.price)
from myparts pa
inner join myprices pr on pr.partid = pa.partid
where pr.PriceDate = (
    select max(PriceDate) 
    from myprices 
    where partid = pa.partid
)

The max() is in case there are multiple prices per day; I'm assuming you'd like to display the highest one. If your price table has an id column, you can avoid the max() and simplify like:

select pa.partid, pa.Partnumber, pr.price
from myparts pa
inner join myprices pr on pr.partid = pa.partid
where pr.priceid = (
    select max(priceid)
    from myprices 
    where partid = pa.partid
)

P.S. Use wcm's solution instead!

Andomar
+1  A: 

All other answers must work, but using your same syntax (and understanding why the error)

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE 
MyPart.PriceDate = (SELECT MAX(MyPrice2.PriceDate) FROM MyPrice as MyPrice2 
WHERE MyPrice2.Partid =  MyParts.Partid)
Eduardo Molteni
+6  A: 

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.

Tom H.
Thanks it is pretty fast. I think above the WHERE it should be PRC2.PriceDate > PRC1.PriceDate though.
MaxGeek
Thanks, I've corrected the typo
Tom H.
Question what does Where PRC2.PartId is null do for the query?
MaxGeek
Forget it, it reduces the number of records returned.
MaxGeek
You seem to have figure it out, but for clarity... it's in effect using a LEFT JOIN as a NOT EXISTS. If no row could be joined, the ID from that table must be NULL.
Tom H.
It's been awhile since this was posted, but THANK YOU. I needed this.
Nikki9696
+2  A: 

On 2005 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

AlexKuznetsov