选择每个组的最大值
选择每个组的最大值
提问于 2010-12-22 22:30:04
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的两个条目，因为它有两个条目具有相同的值。
回答 7
Stack Overflow用户
发布于 2010-12-22 22:32:28
select name, max(value)
from out_pumptable
group by nameStack Overflow用户
发布于 2010-12-22 22:31:58
SELECT
b.name,
MAX(b.value) as MaxValue,
MAX(b.Anothercolumn) as AnotherColumn
FROM out_pumptabl
INNER JOIN (SELECT
name,
MAX(value) as MaxValue
FROM out_pumptabl
GROUP BY Name) a ON
a.name = b.name AND a.maxValue = b.value
GROUP BY b.Name注意:如果你有一个主键，这会容易得多。下面是一个例子
SELECT * FROM out_pumptabl c
WHERE PK in
(SELECT
MAX(PK) as MaxPK
FROM out_pumptabl b
INNER JOIN (SELECT
name,
MAX(value) as MaxValue
FROM out_pumptabl
GROUP BY Name) a ON
a.name = b.name AND a.maxValue = b.value) Stack Overflow用户
发布于 2018-01-26 17:03:55
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4510185
复制相关文章
相似问题