Selecting the latest per group of items

[ryn6]

hi there! i just joined this site and it seems to be very informative.
onto my 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
10000 2009-01-05 50
10000 2009-05-25 48
10000 2010-07-01 40

so in result i should get 10000 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!

[Hack]

Welcome to DevX

What database are you using?

I ask because I'm not familiar with this keyword: USING

[ryn6]

oh by the way im using MySQL