Price database and confusing SQL query

[eluser]Relexx[/eluser]
[quote author="MarcL" date="1234927082"]Sorry for my ignorance but I've been having a look at ActiveRecord and I'm not sure that I can do the inner query with it. Is that right?[/quote]

you could always create a view for this instead

Code:

create view last_prices as select max(created_on) as created, product_id, merchant_id from prices

Then join the view

Code:

select prc.id, prc.price, prd.id, prd.name, mer.name
from prices prc, product prd, merchant mer, last_prices lp
// join the data from the subquery back to the prices table so that you select the max record
// and thus the most recent price
where prc.product_id = lp.product_id
and   prc.merchant_id = lp.merchant_id
and   prc.created_on = lp.created
// then add the joins for the product and merchant tables
and   prd.id = prc.product_id
and   mer.id = prc.merchant_id