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

[CowBelle]

Consider the following table structure:\[code\]id name rank position-------------------------------1 Steve 5 02 David 3 0 3 Helen 9 0 4 Mark 15 0\[/code\]I need a fast algorithm to rate these rows by \[code\]rank\[/code\] column and store the "position" of each row in \[code\]position\[/code\] field in realtime.Now I have a brutal solution:\[code\]SELECT * FROM table ORDER BY rank DESC\[/code\]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?