How to find maximum value per group for all rows in SQL?-CodePudding

How to get the maximum value per group and get all rows of table?

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');

Required output

    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

My attempt

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

CodePudding user response：

You need MAX() window function:

SELECT *, 
       MAX(value) OVER (PARTITION BY name) "max"
FROM out_pumptable;

See the demo.