Given a table:
id | score | position
=====================
1 | 20 | 2
2 | 10 | 3
3 | 30 | 1
What query can I use to optimally set the position columns using the (not nullable) score column?
(I'm sure I've seen this before but I can't seem to get the correct keywords to find it!)
set @rank = 0;
update tbl a join (select score, @rank:=@rank+1 as rank from tbl group by score
order by score desc) b on a.score = b.score set a.position = b.rank;
to update the position in one fell swoop that would do the trick. equal scores get equal position
rank was the keyword I should have used earlier!SELECT * FROM your_table ORDER BY score;
Your position column appears to be redundant, although it is hard to tell from the information given in the question. The functionality you appear to want is accomplished using database row ordering, seen in the above example with the 'ORDER BY' expression. Even if you would want a position column for some good reason, remember that most likely there exists an index for the score column anyway, which would in most cases be doing exactly the same thing that a position column would do. Either that, or I completely misunderstand your question.
Calculate it in the language which you are using. Thus your solution will be:
