Creating a numerical order index on a MySQL table

Question

I have a table with (essentially) 3 columns - name, votes and rank. I want to order the table by votes and then update 'rank' to reflect this order, so that the one with the most votes will have rank set to 1, the second most votes to 2 etc.

I can do this in PHP but it seems pretty wasteful - is there a way to do this with one SQL query without having to manually process every record in PHP?

Answer 1

Assuming you don't care about ties you can just calculate the rank dynamically in your queries instead of storing it.

 SET @rank=0;

 SELECT @rank:=@rank+1 AS rank, name, votes
 FROM yourTable
 ORDER BY votes DESC;

Answer 2

Although @johnfx gave you the correct answer I think you will be interested reading the comments of this article - http://arjen-lentz.livejournal.com/55083.html where various ranking problems are solved.