Skip to content

Instantly share code, notes, and snippets.

@nhatnx

nhatnx/get-records-with-max-value-for-each-group

Last active August 10, 2017 09:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save nhatnx/2fc117557b9ecbd804a280ca9ee20fa9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save nhatnx/2fc117557b9ecbd804a280ca9ee20fa9 to your computer and use it in GitHub Desktop.
Download ZIP
Get records with max value for each group of grouped SQL results
Raw
get-records-with-max-value-for-each-group
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
Raw
get-records-with-max-value-for-each-group.mysql
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment