Jeevan Patnaik Jeevan Patnaik - 29 days ago 9
MySQL Question

how to get latest record or record with max corresponding date of all distinct values in a column in mysql?

For Example, I have table like this:

Date | Id | Total
-----------------------
2014-01-08 1 15
2014-01-09 3 24
2014-02-04 3 24
2014-03-15 1 15
2015-01-03 1 20
2015-02-24 2 10
2015-03-02 2 16
2015-03-03 5 28
2015-03-09 5 28


I want the output to be:

Date | Id | Total
---------------------
2015-01-03 1 20
2014-02-04 3 24
2015-03-02 2 16
2015-03-09 5 28


Here the distinct values are Id. I need latest Total for each Id.

Answer Source

You can use left join as

select 
t1.* from table_name t1
left join table_name t2
on t1.Id = t2.Id and t1.Date >t2.Date
where t2.Id is null

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html