CODEWITHSUNDEEP
sqlsql-server
Gideon
Gideon

Reputation: 313

sql max for column based on value in row

I am trying to get the maximum value in a column based on another value in a row.

I have the following code :

    UPDATE LeweringVsSkattingResultaat
    SET maks = ((SELECT max(persentklaarkultivar2) FROM  
    LeweringVsSkattingResultaat) 
    group by kultivar2)

I get the following error :Incorrect syntax near the keyword 'group'.

I want the maksimum value in column persentklaarkultivar2 for each value in kultivar2.

Any help would be much appreciated.

Regards

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your subquery would generate an error if you have more than on value for kultivar2. The group by would return a row for each kultivar2.

Although you can use a correlated subquery to fix the problem (see end of answer), I like to do this with updatable CTEs and window functions:

with toupdate as (
      select r.*,
             max(persentklaarkultivar2) over (partition by kultivar2) as maxval
      from LeweringVsSkattingResultaat r
     )
update toupdate
     set maks = maxval;

I should note that with window functions, you can readily calculate the maximum whenever you want, so it is not necessary to store it. Window functions are optimized so they can take advantage of an index on LeweringVsSkattingResultaat(kultivar2, persentklaarkultivar2).

This is probably a better approach. You won't have to figure out how to keep the maks value up-to-date when rows are inserted, updated, or deleted from the table.

The correlated subquery would look like:

UPDATE r
    SET maks = (SELECT max(r2.persentklaarkultivar2)
                FROM LeweringVsSkattingResultaat r2 
                WHERE r2.kultivar2 = r.kultivar2
               )
    FROM LeweringVsSkattingResultaat r;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

Remove a () 

 UPDATE LeweringVsSkattingResultaat
    SET maks = ( SELECT max(persentklaarkultivar2) FROM  
    LeweringVsSkattingResultaat 
    group by kultivar2)

otherwise your group by is out the inner select

Upvotes: 0

Related Questions