The correct solution is:
SELECT o.* FROM `Persons` o # 'o' from 'oldest person in group' LEFT JOIN `Persons` b # 'b' from 'bigger age' ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL # bigger age not found
How it works:
It matches each row from
o with all the rows from
b having the same value in column
Group and a bigger value in column
Age. Any row from
o not having the maximum value of its group in column
Age will match one or more rows from
LEFT JOIN makes it match the oldest person in group (including the persons that are alone in their group) with a row full of
b (‘no biggest age in the group’).
INNER JOIN makes these rows not matching and they are ignored.
WHERE clause keeps only the rows having
NULLs in the fields extracted from
b. They are the oldest persons from each group.
This solution and many others are explained in the book SQL Antipatterns: Avoiding the Pitfalls of Database Programming