如何獲取每組的最大值并獲取表的所有行？
create table out_pumptable(
name varchar(255),
value double precision,
anothercolumn varchar(255)
)
insert into out_pumptable
values ('Pump 1', 8000.0, 'Something1');
insert into out_pumptable
values ('Pump 1', 10000.0, 'Something2');
insert into out_pumptable
values ('Pump 1', 10000.0, 'Something3');
insert into out_pumptable
values ('Pump 2', 3043, 'Something4');
insert into out_pumptable
values ('Pump 2',4594, 'Something5');
insert into out_pumptable
values ('Pump 2', 6165, 'Something6');
所需輸出
name value anothercolumn max
Pump 1 8000.0 Something1 10000.0
Pump 1 10000.0 Something2 10000.0
Pump 1 10000.0 Something3 10000.0
Pump 2 3043.0 Something4 6165.0
Pump 2 4594.0 Something5 6165.0
Pump 2 6165.0 Something6 6165.0
我的嘗試
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
uj5u.com熱心網友回復：
您需要MAX()視窗功能：
SELECT *,
MAX(value) OVER (PARTITION BY name) "max"
FROM out_pumptable;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413588.html
標籤：