CODEWITHSUNDEEP
sqlgreatest-n-per-group
kdudeIA
kdudeIA

Reputation: 57

Find a value based on the minimum in a group SQL

I want to find the names of the youngest person within a group of rankings. Everything is in the table Person, with fields PName(various), PAge(various), PRanking(0-3). I wish to find the name of the youngest person in each ranking group (0-3). Therefore, if there is a person with a ranking in each group, the query will return 4 names (the youngest person in each ranking).

My approach so far is:

SELECT PName
FROM Person
WHERE PAge = MIN(PAge)
GROUPBY PRanking

Upvotes: 0

Views: 83

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This is not a group by problem. This is a filtering problem. So, think where, not group by:

SELECT p.*
FROM Person p
WHERE PAge = (SELECT MIN(p2.PAge) FROM Person p2 WHERE p2.PRanking = p.PRanking);

With an index on Person(Pranking, PAge), this is likely to have performance as least as good as any other method.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could join the subselect for the min age 

   select P.PName from Person P
    inner join (
    SELECT ranking, min(age)  min_age
    FROM Person
    GROUP BY PRanking 
    ) t on t.ranking  = p.ranking  and t.min_age = p.age

Upvotes: 2

Mohammed HIMMI
Mohammed HIMMI

Reputation: 39

SELECT p.* 
FROM Person p 
WHERE p.PAge,p.Pgroup in =
  (SELECT MIN(p2.PAge), p2.Pranking FROM Person P2 Group by P2.pranking);

Upvotes: 0

Related Questions