Selecting the latest per group of items

Question

i have 2 tables products and cost

PRODUCT

ProdCode - PK
ProdName

COST

Effectivedate - PK
RetailCOst
Prodcode

i tried this query:

SELECT a.ProdCOde AS id, MAX(EffectiveDate) AS edate, RetailCOst AS retail 
FROM cost a 
INNER JOIN product b USING (ProdCode)
WHERE EffectiveDate <= '2009-10-01'
GROUP BY a.ProdCode;

uhm yah its showing the right effectivedate but the cost on that specific effectivedate doesnt match.

so i want to select the latest date with the matching cost per item.

for example the date i selected is '2009-12-25' and the records for 1 item are these:

ProdCode |EffectiveDate| Cost
00010000 | 2009-01-05  |    50
00010000 | 2009-05-25  |    48
00010000 | 2010-07-01  |    40

so in result i should get 00010000|2009-05-25|48 because it is lesser than the date on my query and it is the latest for that item. and then i want to to show on my query the latest costs on each product.

hope to hear from you soon! thanks!

Answer 1

some help please..

Answer 2

You need to use a subquery here:

SELECT maxdates.ProdCode, maxdates.maxDate, cost.RetailCost as retail
   SELECT ProdCode, max(EffectiveDate) as maxDate
   FROM cost 
   WHERE EffectiveDate < '2009-10-01'
   GROUP BY ProdCode
) maxdates
LEFT JOIN cost ON (maxdates.ProdCode=cost.ProdCode
               AND maxdates.maxDate=cost.EffectiveDate)

Explanation: The inner SELECT gives a list of all Products and their respective maximum EffectiveDates. The join "glues" the retail cost per data entry to the result.

Answer 3

Alternatively, using the old max concat trick should do the trick.

SELECT
  p.ProdCode,
  SUBSTRING(MAX(CONCAT(d.EffectiveDate, c.RetailCost)), 1, 10) AS date,
  SUBSTRING(MAX(CONCAT(d.EffectiveDate, c.RetailCost)), 10, 100) + 0 AS cost
FROM
  product  p,
  cost     c
WHERE
  p.ProdCode = c.ProdCode AND
  c.EffectiveDate < '2009-10-01'
GROUP BY   
  p.ProdCode