选择每个组的最大值

Question

Name    Value   AnotherColumn

-----------

Pump 1  8000.0  Something1

Pump 1  10000.0 Something2

Pump 1  10000.0 Something3

Pump 2  3043    Something4

Pump 2  4594    Something5

Pump 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_pumptablegroup by name

Answer 2

select name, value from( select name, value, ROW_NUMBER() OVER(PARTITION BY name ORDER BY value desc) as rnfrom out_pumptable ) as awhere rn = 1