Oct 10, 2018

[HDGEM] How to get records with max value for each group of grouped SQL results

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

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 b.

The LEFT JOIN makes it match the oldest person in group (including the persons that are alone in their group) with a row full of NULLs from b ('no biggest age in the group').

The WHERE clause keeps only the rows having NULLs in the fields extracted from b. They are the oldest persons from each group.

Posted By Blogger to HDGEM at 3/07/2017 12:30:00 PM