Merging when using SQL GROUP BY

Question

my data in the mySQL DB looks like this (but not only this 4, i have many persons which are appearing more then once, but with different qualifications and different modified dates

Selection would be something like:

SELECT * FROM table where person_id=1 GROUP BY person_id

so, if i make this selection and i group by person_id i get something like this:

is there any possibility to group by the person id, but to say: ok, give me the last modified qualification? normally i don't have the person_id in the where SQL statement.
so i want all persons from my selection but only the result with the last modified qualification
( I hope I explained it well eonough so you understand what the problem is )

(my selection is of course much more complicated then the one i showed, this is just for showing you what I'm doing)

Answer 1

You can also use a subquery to return the max modified date for each person_id and then join that result to your table to get the rows with the max date:

select t1.id, 
  t1.person_id,
  t1.name,
  t1.qualification,
  t1.created,
  t1.modified,
  t1.version
from yourtable t1
inner join
(
  select person_id, max(modified) MaxMod
  from yourtable
  group by person_id
) t2
  on t1.person_id = t2.person_d
  and t1.modified = t2.maxmod