Price database and confusing SQL query

[eluser]kgill[/eluser]
Using asc or desc to solve this is the wrong way to go about it, that will give you all the prices including old ones but just stick them at the start or end of the list. The reason the other query didn't work is that the price is different for each of the dates so you can't include it in the same query as the max() & group by statements.

Anytime you want to get the most recent set of values for a given group where one of the columns is dependent on the date you're looking at a subquery.

Code:

select prc.id, prc.price, prd.id, prd.name, mer.name
from prices prc, product prd, merchant mer,
     (// the subquery finds the highest date for each merchant for a given product
      select max(created_on) as created, product_id, merchant_id
      from prices
      where product_id = $product_id
      group by product_id, merchant_id
     ) max
// 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 = max.product_id
and   prc.merchant_id = max.merchant_id
and   prc.created_on = max.created
// then add the joins for the product and merchant tables
and   prd.id = prc.product_id
and   mer.id = prc.merchant_id

// You could do this using JOIN's but I find nesting that many gets ugly fast.

Now the disclaimer, I work with Oracle on a daily basis so you might have to alter that query a bit.