Price database and confusing SQL query

[eluser]MarcL[/eluser]
I read that the people on the Code Igniter website were helpful but I didn't expect replies in under an hour. Thanks guys!

@umefarooq

Yes, there will be products with multiple prices from multiple merchants. I'm trying to record the change in prices over time and find out which merchant is the cheapest. Like a price comparison website.

@obiron2

Thanks for the SQL. But the 'get latest prices' hasn't quite worked.
The data I've got in the prices array for a certain product id (435) is:

Code:

product_id  merchant_id  price     created_on
435         4            29.97     2009-02-16 13:01:48
435         3            11.99     2009-02-16 13:02:02
435         4            26.99     2009-02-16 21:07:10
435         5            23.75     2009-02-16 21:21:28

When I run the query I get

Code:

merchant_id price   max(p.created_on)       name    description
3           11.99   2009-02-16 13:02:02     Name1   desc1
4           29.97   2009-02-16 21:07:10     Name2   desc2
5           23.75   2009-02-16 21:21:28     Name3   desc3

So merchant_id 4 doesn't have the latest price. Instead it has a combination of the first price and the second (and latest) created_on date/time.

Any ideas?