选择每个组(包括其他列)的最大值 [英] select max value of each group including other column

问题描述

选择每个组的最大值

在提到的问题中，有一个解决方案可以获取每个组的最大值.我需要更多帮助，我希望每个组的最大值以及该最大值的另一列(有问题的第三列).桌子看起来像这样

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

输出应为

Name  Value AnotherColumn
Pump1 2000    2
Pump2 2000    1

我正在使用Microsoft Sql Server2012.另一列可以是任何类型，它不限于整数，我只想获取另一列以获取每个组的最大值.

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.

推荐答案

一个选项使用 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

请注意，如果您希望每个名称的所有联系都具有最大值，则可以将 ROW_NUMBER 替换为 RANK (或也许将 DENSE_RANK )，以获得所有联系.

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.

这篇关于选择每个组(包括其他列)的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！