title: Simple MySQL Update Rank with Ties
body: <p>I am attempting to store the rank of users based on a score, all it one table, and skipping ranks when there is a tie. For example:</p>
  
  <pre><code>ID   Score Rank
  2    23    1
  4    17    2
  1    17    2
  5    10    4
  3    2     5
  </code></pre>
  
  <p>Each time a user's score is updated, The rank for the entire table must also be updated, so after a score update, the following query is run:</p>
  
  <pre><code>SET @rank=0;
  UPDATE users SET rank= @rank:= (@rank+1) ORDER BY score DESC;
  </code></pre>
  
  <p>But this doesn't support ties, or skipping rank numbers after ties, for that matter.</p>
  
  <p>I want to achieve this re-ranking in as few queries as possible and with no joins (as they seem rather time consuming).</p>
  
  <p>I was able to get the desired result by adding two columns - last_score and tie_build_up - with the following code:</p>
  
  <pre><code>SET @rank=0, @last_score = null, @tie_build_up = 0;
  UPDATE users SET
      rank= @rank:= if(@last_score = score, @rank, @rank+@tie_build_up+1),
      tie_build_up= @tie_build_up:= if(@last_score = score, @tie_build_up+1, 0),
      last_score= @last_score:= score, ORDER BY score DESC;
  </code></pre>
  
  <p>I don't want those extra columns, but I couldn't get the single query to work without them.</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks.</p>