Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
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.7k 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.

1.2k questions

2.7k answers

501 comments

693 users

Browse Categories

...