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

Solution 1:

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.Datewhere t2.Id is null

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

Solution 2:

You can also use Max() in sql:

SELECTdate, id, total
   FROMtableas a WHEREdate= (SELECTMAX(date)
   FROMtableas b
  WHERE a.id = b.id
 )

Solution 3:

You can do it as below

SELECT*FROM YourTable D
WHEREdate= (SELECTMAX(date) FROM YourTable WHERE ID = D.ID)

Solution 4:

Another way is by using INNER JOIN

Find the latest date per ID then join result back to the table to get the value

select A.ID,A.Date,A.value 
from yourtable A 
INNER JOIN 
(
select MAX(date) asDate,ID 
from yourtable
groupby ID 
) B
ON A.ID =B.ID and A.Date = B.Date

Solution 5:

The other answers didn't work for me. I found the following code, which worked great for me:

SELECT*FROMTABLEWHEREDATEIN (SELECTMAX(DATE) FROMTABLE)

I am using SSMS 2014, SQLServer