Choose the minimum value in the 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 the lines where pay1 + pay2 is minimal for each 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 this in SQL Server? Thanks

Answer 1

Use the 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 a demo .

Answer 2

The OP does not mention how possible relationships can be handled, 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 Jan's answer, which is clearer.

SQLFiddle with a demo.

Answer 3

Here is another option.

In this display, links for minimum values ​​will be displayed. As @MitchWheat points out, the question does not indicate how to handle them.

 SELECT a.Id, a.Name, a.Pay1, a.Pay2 FROM [table] a INNER JOIN ( SELECT Name, MIN(Pay1 + Pay2) as MinPay FROM [table] GROUP BY Name ) b ON a.Name = b.Name AND a.Pay1 + a.Pay2 = b.MinPay