junior junior - 6 months ago 8
MySQL Question

SQL query returns wrong result set

I have this table:

columns:
Id product_id name status update_date

1 1 prod1 bought 2016-04-20 10:10:10
2 1 prod1 sold 2016-04-22 12:25:00
3 1 prod1 sold 2016-06-03 09:42:15


I wanna execute this query:

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


I get:

1 prod1 bought 2016-04-20 10:10:10
2 prod1 sold 2016-06-03 09:42:15


For the second row in the result set, I have to get:

3 prod1 sold 2016-06-03 09:42:15


and not:
2 prod1 sold 2016-06-03 09:42:15
!

Answer

Try this;)

select id,name,status,update_date from product
where (name, status, update_date) in (
    select name,status,max(update_date) from product
    group by name,status
)

Or

select t1.id, t1.name, t1.status, t1.update_date
from product t1
inner join (
    select name,status,max(update_date) as update_date from product
    group by name,status
) t2 on t1.name = t2.name and t1.status = t2.status and t1.update_date = t2.update_date