Selecting the latter for each group of elements

Question

Possible duplicate:
Getting the last record in each group

I have 2 product tables and cost

Products

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 showing the correct action, but the cost of this particular effect does not match.

so I want to select the most recent date with the corresponding cost per item.

for example, the date I selected is '2009-12-25', and the entries for 1 item:

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

therefore, as a result, I should get it 00010000|2009-05-25|48, because it is less than the date of my request and is the last for this element. and then I want to show on my request the last costs for each product.

hope to hear from you soon! thank!

Answer 1

:

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)

: SELECT . "" .

Answer 2

, max concat .

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