Giving mysql database column rank

Question

I have a column in my database titled PositionThisWeek.

I want to assign rank to this column from the values of another column titled Points.

The rank should be from 1 upwards, how can I update the Position column?

Answer 1

One way to calculate the rank will be to use a variable, for instance:

select Userid, Points, @rank := @rank + 1 AS rank
from PointsTable, (select @rank := 0) r
order by Points desc;

Here is an SQL Fiddle which does the update in one way.

(Kudos to Daniel Vassallo for the idea of putting the initialisation into the select!)