nhat-x
8/10/2017 - 9:16 AM

Get records with max value for each group of grouped SQL results

Get records with max value for each group of grouped SQL results

get-records-with-max-value-for-each-group.mysql
content_copyfile_download
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
get-records-with-max-value-for-each-group
content_copyfile_download
Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39


--> Desired result set:

Shawn | 1     | 42    
Laura | 2     | 39