Getting the latest entries for grouped by the foreign key in MySQL

Question

Hi! I got the table with the fields: id, foreign_key, created, modified

It's a table that logs the changes of a part of my program. What I want is to retrieve the latest logs grouped by the foreign key. How do I do this?

EDIT: to summarize, how do I order first before I group?

Answer 1

SELECT ... GROUP BY ... HAVING MAX(modified)

Answer 2

Uncorrelated subquery:

SELECT t.*
       , a.*
  FROM mytable t
       INNER JOIN 
           (select b.foreign_key
                   , max(b.modified) as max_modified 
              from mytable b 
           group by 1) a
       ON a.foreign_key = t.foreign_key 
      AND a.max_modified = t.modified