You can do this with a Correlated Subquery (That is a subquery wherein you reference a field in the main query). In this case:
SELECT *
FROM yourtable t1
WHERE date = (SELECT max(date) from yourtable WHERE id = t1.id)
Here we give the yourtable
table an alias of t1
and then use that alias in the subquery grabbing the max(date)
from the same table yourtable
for that id
.