CODEWITHSUNDEEP
sqlsql-serverrank
user618520
user618520

Reputation: 139

Resort the rank after updating it

There is a student table with their scores:

studentid    studentscore    rank
----------------------------------
1             77              6
2             80              4
3             90              2
4             94              1
5             78              5
6             88              3

However, the score of id 1 and 5 are wrong, id 1 should be 78 and id 5 should be 77.

I update their scores, but how to resort their ranks?

Upvotes: 1

Views: 72

Answers (3)

VBoka
VBoka

Reputation: 9083

Here is the update statement you can use:

update students
set rankc = T1.Ranking
from students T2
left join (select RANK() OVER (ORDER BY studentscore desc) as Ranking
                  , studentid
           from students a ) T1
on T1.studentid   = T2.studentid

Here is the DEMO

Please note that I have renamed your column "rank" into "rankc" for this example.

Upvotes: 1

Nick
Nick

Reputation: 147166

As @Mureinik points out, you can just generate rank on the fly. However if you need to store it (possibly for performance reasons) you can update the table using a CTE with the new ranks:

WITH ranks AS (
  SELECT studentid, RANK() OVER (ORDER BY studentscore DESC) AS [rank]
  FROM students
)
UPDATE s
SET s.[rank] = r.[rank]
FROM students s
JOIN ranks r ON s.studentid = r.studentid

After updating the studentscore values for studentid 1 and 5, this is the output:

studentid   studentscore    rank
1           78              5
2           80              4
3           90              2
4           94              1
5           77              6
6           88              3

Demo on dbfiddle

Upvotes: 1

Mureinik
Mureinik

Reputation: 311228

An easier approach could be to not store the rank at, but query it dynamically when you need it:

SELECT studentid, studentscore, RANK() OVER (ORDER BY studentscore)
FROM   mytable

Upvotes: 2

Related Questions