How to update one row that has max value in column (SQL Server)

I am trying to update the Rows that have the Max score with the value of ‘Yes’ in the Text1 column.

This is straightforward except when there are multiple rows with the max score.

If they have the same score, I just want to select the top row to have the value ‘Yes’. Only one row with identical Vendor IDs should have the ‘Yes’ value.

    UPDATE Suppliers
        SET Text1='Yes'
        --SELECT DISTINCT *
        FROM Suppliers INNER JOIN
            (
            SELECT Vendor, MAX(VCScore) as MaxVCScore
            FROM Suppliers 
            GROUP BY Vendor
            ) maxTable
        ON Suppliers.Vendor=maxTable.Vendor
        AND Suppliers.VCScore=maxTable.MaxVCScore

I do not want to use TOP 1 because that will only update one row in the whole table. I instead want only one row for each Vendor to be updated. (Vendor can be identical which is what I am trying to fix.) I cannot add a Group By clause to the Update statement as I would like to group by Vendor but that is incorrect syntax.

Answer

with t as (
select * , row_number() over (partition by Vendor order by VCScore desc) rn
from Suppliers
)

update s
set Text1 = 'Yes'
from supplier s
join t on s.pkey = t.pkey and t.rn = 1

Advertisement

Answer