Back

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.

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.

Related questions

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

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...