I have a table that looks something like this:
date | product | price
--------------------------------
17/01/2015 | milk | 2.54
18/01/2015 | milk | 2.47
23/01/2015 | milk | 2.61
21/01/2015 | eggs | 1.35
04/02/2015 | eggs | 1.36
27/01/2015 | eggs | 1.31
What I need is a select that returns me the latest price of each product, that is the one with the maximum date. Desired result here would be:
23/01/2015 | milk | 2.61
04/02/2015 | eggs | 1.36
I tried this:
select max(date), product, price FROM table GROUP BY product, price
but it didn't work as expected.
Use a correlated subquery to find each product's last date:
select date, product, price
from table t1
where date = (select max(date) from table t2
where t1.product = t2.product)
(After reading Damien_The_Unbeliever's comment I want to add that if several entries exist with same max date for a product, they will all be returned.)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments