Select minimum value in group

Question

I have a table

ID,  name, pay1, pay2
 1     a,    3,    2
 2     b,   12,    4
 3     b,    4,    8
 4     c,    8,    7
 5     c,    5,    2
 6     a,    7,    1

I would like to select rows where pay1 + pay2 is minimum for every name. So, I would like to get

 ID,  name, pay1, pay2
 1     a,    3,    2
 3     b,    4,    8
 5     c,    5,    2

Any idea how to do that in SQL Server? Thanks

Answer 1

Use a ranking function:

with minpay as
(
  select *
    , payrank = row_number() over (partition by name order by pay1 + pay2, ID)
  from pay
)
select ID
  , name
  , pay1
  , pay2
from minpay
where payrank = 1
order by name

SQL Fiddle with demo.

Answer 2

OP doesn't mention how possible ties are to be treated, but:

select t1.* 
from table1 t1 join
(select id, sum(pay1 + pay2) as sumpay 
from table1  
group by id) s on t1.id = s.id
join 
(select name, min(pay1 + pay2) as sumpay 
from table1 
group by name) t on t.sumpay = s.sumpay 

I posted this and then saw Ian's answer which is clearer.

SQLFiddle with Demo.