mySql: a faulty returned result -

i have 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 following query:

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

i 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 product product_id = 1, status bought , not sold!!

i wanna last status each product.

try this:

select  p.status, p.update_date, p.product_id product p inner join  ( select  max(update_date) max_time,  product_id  product  group product_id ) t on p.product_id = t.product_id , p.update_date = t.max_time 

it lies in category : select whole row having max value.

please @ post