neila neila - 7 months ago 9
SQL Question

mySql: a faulty returned result

I have this table:

product_id | name | status |update_date
___________|________|___________|___________
1 | prod1 | bought | 2016-04-20
2 | prod2 | bought | 2016-04-20
3 | prod3 | bought | 2016-04-20
1 | prod1 | sold | 2016-04-22


I execute the following query:

select status, max(update_date), product_id from product group by product_id;


I get this result:

bought| 2016-04-22 12:25:00 | 1
bought| 2016-04-20 10:10:10 | 2
bought| 2016-04-20 10:10:10 | 3


I wonder why for the product with product_id = 1, I get the status bought and not sold!!

I wanna get the last status for each product.

Answer

Try this:

SELECT 
P.status,
P.update_date,
P.product_id
FROM product P
INNER JOIN 
(
select 
max(update_date) max_time, 
product_id 
from product 
group by product_id ) t
ON P.product_id = t.product_id AND P.update_date = t.max_time

It lies in this category : SELECT THE WHOLE ROW HAVING THE MAX VALUE.

Please look at this POST