Mysql查询按组最新日期搜索

Question

This is my table structure

and this is my dataset

What I want is query that gets data ordered by date desc and group by id_patient

so the result in the dataset example should be like this:

Answer 1

I would go with limit clause with subquery since you have PK :

select *
from table t
where id = (select t1.id
            from table t1
            where t1.id_patient = t.id_patient
            order by t1.date desc
            limit 1
           );

However, if single patient has multiple same dates then this would produce only single records based on date.