How to sort rows in a table and fetch row positions?

Question

Consider the following table structure:

id    name     rank    position
-------------------------------
1     Steve    5        0
2     David    3        0 
3     Helen    9        0 
4     Mark    15        0

I need a fast algorithm to rate these rows by rank column and store the "position" of each row in position field in realtime.

Now I have a brutal solution:

SELECT * FROM table ORDER BY rank DESC

And then fetch the result in a loop and update every row filling the position column. But what If I'd have thousands of entries? How can I optimize it?

Answer 1

Pick one:

WITH t AS
(
SELECT 
  ROW_NUMBER() OVER (ORDER BY rank) as RowNum
  ,RANK() OVER (ORDER BY rank) as RankNum
  ,DENSE_RANK() OVER (ORDER BY rank) as DenseRankNum
  , * 
FROM table
)
UPDATE t set position = RankNum
--UPDATE t set position = RowNum
--UPDATE t set position = DenseRankNum
;

Answer 2

I answered a very similar question specific to MySQL a few days ago:

http://stackoverflow.com/questions/2067995/automate-mysql-fields-like-excel

The trick is not to store the column in the database but to calculate it dynamically when you fetch the results. Alternatively you could use triggers to update the column every time you make an insert, update or delete.