选择每个组的最大值

Question

Name    Value   AnotherColumn-----------Pump 1  8000.0  Something1Pump 1  10000.0 Something2Pump 1  10000.0 Something3Pump 2  3043    Something4Pump 2  4594    Something5Pump 2  6165    Something6我的桌子看起来像这样。我想知道如何为每个泵选择最大值。select a.name, value from out_pumptable as a,(select name, max(value) as value from out_pumptable where group by posnumber)g where and g.value = value这段代码可以完成工作，但是我得到了Pump 1的两个条目，因为它有两个具有相同值的条目。

Answer 1

select name, max(value)

from out_pumptable

group by name

Answer 2

select name, value 

from( select name, value, ROW_NUMBER() OVER(PARTITION BY name ORDER BY value desc) as rn

from out_pumptable ) as a

where rn = 1