Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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  

1 Answer

0 votes
by (40.4k points)

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



FROM yourTable t1



    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.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers


94.2k users

Browse Categories