CODEWITHSUNDEEP
sql-servert-sqlgroup-by
KVM
KVM

Reputation: 919

T-SQL get column that matches aggregate in group by

I made a very simple sample http://sqlfiddle.com/#!6/d2cc0/4

I have a table "People" with a name, age and weight. I want to retreive the name of the lightest person of each age.

I am grouping people by age so I can retreive the weight of the lightest person at every different age, but how can I retreieve the name that was matched by the min() aggregate ?

Upvotes: 0

Views: 48

Answers (2)

APH
APH

Reputation: 4154

Use partitions:

Select * from (
    Select *
    , min(weight) over (partition by age) as MinWeight 
    from People) a
where Weight = MinWeight

or:

Select * from people a
where weight = (select min(weight) from people b where a.age = b.age)

Note that both will return more than one person per age if there are ties.

Upvotes: 1

Raishul
Raishul

Reputation: 146

Following query will return name, age and minimum weight :

SELECT P.* from People P JOIN (SELECT
  age,
  min(weight) as lightest
FROM
  People 
GROUP BY age) T on p.age = T.age and p.weight = T.lightest
Output:

| name | age | weight |
|------|-----|--------|
|    A |  20 |     60 |
|    C |  25 |     70 |

Upvotes: 1

Related Questions