Thread: JOIN on most recent entry

Is it possible to JOIN a table only retrieving the most recent entry from the joined table?

If I have some data like:-
tblProducts

Code:

ID         Name    
1         Blue Widget
2         Red Widget

tblPrices

Code:

ID    ProductID      Price                TimeStamp
1          1         32            11/11/2009 12:35:36
2          2         65            11/11/2009 12:36:47
3          1         35            11/11/2009 13:25:56

Is it possible to do a JOIN so that only the most recent price is retrieved for Blue Widget. Something like

Code:

ProductID           Name                   Price
     1            Blue Widget               35
     2            Red Widget                65

I have a query of

Code:

SELECT
tblproducts.ID,
tblProducts.Name,
tblPrices.Price

FROM tblproducts
RIGHT JOIN tblPrices ON tblPrices.ProductID=tblProducts.ID

How can I only get the most recent Price for each product?

You'll need to use a sub-query to obtain the most-recent record from the price table:

Code:

Select A.ID, A.ProductID, A.Price A.TimeStamp
 From tblPrices A
 Join (Select ProductID, Max(TimeStamp) as TimeStamp
         From tblPrices) B
     On (A.ProductID = B.ProductID and A.TimeStamp = B.TimeStamp)

Off the top of my head, this should do it.

I wondered if there might be a way of doing it without a sub query. Couldn't quite get it to work your way, but used this instead:-

Code:

SELECT
tblproducts.ID,
tblProducts.Name,
tblPrices.Price

FROM tblproducts
RIGHT JOIN tblPrices ON
(tblPrices.ProductID=tblProducts.ID AND tblPrices.TimeStamp=
(SELECT MAX(TimeStamp) FROM tblPrices WHERE tblPrices.ProductID=tblProducts.ID))

That's kinda along the same lines of thinking in that you use the TimeStamp and ProductId as the join values (the key idea to the Most Recent Record query). However, I should point out that your example uses a sub-query as well

Actually, that didn't work quite as I wanted. In my attempt to oversimplify the example, it's not retrieving all the results I want.

There are several other tables that are linked to the products related to different currencies and weights of a product it can be sold in.

So, I want to be able to retrieve a price for each product in each currency and in each weight.

I also have a weights table like so:-
Weights

Code:

  ID     ScaleID    Amount
  1          1          10.000
  2          1          50.000

Scales

Code:

  ID     ShortCode
  1          mg

So the full prices table might look something like

Code:

ID    ProductID      Price                TimeStamp       WeightID
1          1         32            11/11/2009 12:35:36         1
2          2         65            11/11/2009 12:36:47         1
3          1         35            11/11/2009 13:25:56         1
4          1         150          11/11/2209 12:37:25         2

In Total I'd want the result to be:-

Code:

ProductID           Name                   Price     Weight
     1            Blue Widget              150           50
     1            Blue Widget               35            10
     2            Red Widget                65            10

it was only retrieving one price entry for each product.
I have this query:-

Code:

SELECT
A.ID,
A.Name,
A.Description,
C.CurrencySymbol,
B.Price,
B.DtAdded,
D.Amount,
E.ShortName

FROM tblProducts A
RIGHT JOIN tblProductPrices B
ON (B.ProductID=A.ID AND B.dtAdded=
(SELECT MAX(dtAdded) AS spDtAdded FROM tblProductPrices WHERE tblProductPrices.ProductID=A.ID))
LEFT JOIN tblsysCurrencyCode C ON C.ID=B.CurrencyID
LEFT JOIN tblsysProductWeights D ON D.ID=B.WeightID
LEFT JOIN tblsysWeightScales E ON E.ID=D.ScaleID

This is returning an entry for each weight element and then joining a product to it.
Hope that makes sense. I tried to simplify the explanation to make it easier to understand, but perhaps giving all the facts from the start might have been better. May be a matter of just changing a couple of the JOINS around.

I don't have time to make up and example here, but if you go back to my self join example, the WeightID can be factored in by adding it to the GROUP BY expression. In essence you want to GROUP BY those fields that make your record unique save for the DateTime and Primary Key.

OK. After much hair pulling I came up with this:-

Code:

SELECT
A.ID,
A.MainCode,
A.SubCode,
A.Name,
A.Description,
C.CurrencySymbol,
B.Price,
B.DtAdded,
B.UserID,
D.Amount,
E.ShortName

FROM tblProducts A
JOIN tblProductPrices B
ON (B.ProductID=A.ID AND
B.DtAdded=
(SELECT MAX(DtAdded) FROM tblProductPrices Z WHERE Z.ProductID=A.ID
AND Z.WeightID=B.WeightID
)
)
LEFT JOIN tblsysCurrencyCode C ON C.ID=B.CurrencyID
LEFT JOIN tblsysProductWeights D ON D.ID=B.WeightID
LEFT JOIN tblsysWeightScales E ON E.ID=D.ScaleID

Tried grouping but the problem was with the sub query. I had to get it to give me the MAX DtAdded based on all the necessary criteria.

Just doesn't seem very efficient to me, when you have a sub query that is querying the same table you're using in the JOIN.

I was thinking about maybe having a seperate price history table that INSERTS the old data when a price gets updated. So the ProductPrices table would hold a current record for each product / currency / weight combination. But then the historical table would have to hold the same fields, so you know which product, currency and weight it is referring to.

Any suggestions on a better way of doing it?

OK. Just did another test of adding a different currency price for another product / weight combination and I had to make the sub query into this:-

Code:

B.DtAdded=
(SELECT MAX(DtAdded) FROM tblProductPrices Z WHERE Z.ProductID=A.ID
AND Z.WeightID=B.WeightID
AND Z.CurrencyID=B.CurrencyID
)

So, for each variable in a product, I'm going to have to add a WHERE clause.

I would have thought there ought to be a better way of doing this without the subquery, but I can't think of it.

Anyone?