Update max with condition-CodePudding

I have table1

grade	sums	rate
2	197
4	187
4	154
2	1025
5	934
6	354
3	325
4	405
4	178
3	97
3	81
2	132
3	454
5	90

I am trying to get the max value of column sums based on the criteria in column grade and the output will be something like this;

grade	sums	rate
2	197	1,025
4	187	405
4	154	405
2	1025	1,025
5	934	934
6	354	354
3	325	454
4	405	405
4	178	405
3	97	454
3	81	454
2	132	1,025
3	454	454
5	90	934

Tried this but getting error

update table1 
set rate = (select MAX(sums) OVER(PARTITION BY grade) FROM table1);

CodePudding user response：

I'm not sure if a CTE would be any faster than @Hieko Jakubzik's query in the comments. But maybe? In this query, we find all of the maxes and they go in a temporary table (tt).

WITH tt as ( SELECT grade, max(sums) AS thisrate
               FROM table1
           GROUP BY grade )

UPDATE table1 t 
   SET rate = thisrate
  FROM tt
 WHERE t.grade = tt.grade