0

Select max value of each group

In the mentioned Question there is solution to take the max value of each group. I need some more help I want both the max value of each group and another column(third column in question) for that max value. Table looks like this

Name  Value AnotherColumn
Pump1 1000   1
Pump1 2000   2
Pump2 1000   2
Pump2 2000   1

Output should be

Name  Value AnotherColumn
Pump1 2000    2
Pump2 2000    1

I am using Microsoft Sql Server 2012. Another column can be of any type, it is not restricted to integer, I just want to get the another column for the max value of each group.

Nadeem
  • 379
  • 1
  • 13

4 Answers4

2

One option uses ROW_NUMBER:

SELECT Name, Value, AnotherColumn
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Value DESC) rn
    FROM yourTable
) t
WHERE rn = 1

Note that if you want all ties per name with regard to largest value, then you may replace ROW_NUMBER with RANK (or maybe DENSE_RANK), to get all ties.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

Use row_number() function:

select * from 
(select *, row_number() over (partition by name order by value desc) as rn)a
where rn=1
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

simply use group by and max() 

select name ,max(value), max(AnotherColumn) 
from t group by Name

you can use co-related subquery

 select * from t t1
   where t1.value in (select max(t2.value) from t t2 where t1.name=t2.name)
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

You can try to use a subquery in where to make it. 

SELECT * 
FROM T t1
WHERE Value = (
    SELECT MAX(Value) 
    FROM T tt
    WHERE tt.Name  = t1.Name  
)
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • let us assume if a max value of one group is 3 but other group also has it but its not max of that group. Both will get selected then and also = should not be operator in the query. – Nadeem Sep 24 '18 at 10:31