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

Question

How do you get the rows that contain the max value for each grouped set?

I've seen some overly-complicated variations on this question, and none with a good answer. I've tried to put together the simplest possible example:

Given a table like that below, with the person, group, and age columns, how would you get the oldest person in each group? (A tie within a group should give the first alphabetical result)

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  

Answer 1

You can try joining against a subquery that will be helpful to pull the MAX(Group) and Age.

QUERY:

SELECT t1.*

FROM yourTable t1

INNER JOIN

(

    SELECT `Group`, MAX(Age) AS max_age

    FROM yourTable

    GROUP BY `Group`

) t2

    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;

Note:  The above method is portable across most of the RDBMS.

Enroll yourself in the best sql courses to learn in-depth about SQL statements, queries and become proficient in SQL.