MySQL update statement to store ranking positions
In MySQL, you can use row_number.
Here's an example of using it in a SELECT
:
select @rownum:=@rownum+1 ‘rank’, p.*
from player p, (SELECT @rownum:=0) r
order by score desc;
If you INSERT INTO
using a SELECT
like this, you will get your rankings.
Here's a way to do it:
SET @r=0;
UPDATE table SET Ranking= @r:= (@r+1) ORDER BY Score DESC;
/* use this if you just want to pull it from the db, but don't update anything */
SET @r=0;
SELECT *, @r:= (@r+1) as Ranking FROM table ORDER BY Score DESC;