Ordinal Ranking in MySQL Update

Question

I've got a table like this:

id    name    incidence    placeRef
1     John    10           1
2     Ann     9            1
3     Paul    9            1
4     Carl    8            1
5     John    4            1
6     Ann     4            1
7     Paul    7            1
8     Carl    1            1

I want to rank these using the ordinal ranking method. Which would add ranks to my table as such:

id    name    incidence    placeRef    rank
1     John    10           1           1
2     Ann     9            1           2
3     Paul    9            1           2
4     Carl    8            1           4
5     John    4            1           2
6     Ann     4            1           2
7     Paul    7            1           1
8     Carl    1            1           4

How can this be achieved?

N.B. I am going to answer my own question, but would like to know if anyone has any better solutions as it is a bit hacky; though I found numerous posts recommending hacks for this situation.

Answer 1

You can do this with variables or a correlated subquery. The subquery method looks like this:

select t.*,
       (select 1 + count(t2.incidence)
        from table t2
        where t2.incidence > t.incidence
       ) as rank
from table t;

The variables method is a bit trickier because you have to remember the number of matching rows with a given value:

  select t.*,
         (@rn := if(@i = t.incidence, if(@cnt := @cnt + 1, @rn, @rn),
                    @cnt + if(@i := t.incidence, if(@cnt := 1, @rn, @rn), @rn)
                   )
         ) as rank
  from table t cross join
       (select @i := 0, @rn := 0, @cnt := 1) vars
  order by incidence desc;

EDIT:

If you want to update the table, just use update with a join:

update table t join
       (<either subquery above>) s
      on t.id = s.id
    set t.rank = s.rank;

Answer 2

The following works:

UPDATE names
JOIN ( SELECT * FROM names ORDER BY placeRef, incidence DESC ) AS p ON p.id = names.id,
( SELECT @curRank := 0, @nextRank := 0, @prevInc := 9999999999, @prevPlace := 0 ) AS v
SET 
names.rank = IF(  @prevPlace != p.placeRef, @curRank := 0, 0 ),
names.rank = IF(  @prevPlace != p.placeRef, @nextRank := 0, 0 ),
names.rank = IF(  @prevInc = p.incidence, @nextRank := @nextRank + 1, @curRank := @nextRank := @nextRank + 1 ),
names.rank = IF(  @prevInc = p.incidence, @curRank := @curRank, @curRank := @nextRank ),
names.incidence = @prevInc := names.incidence,
names.placeRef = @prevPlace := names.placeRef;

Explanation:

UPDATE names

1 - Sets the table to be updated

JOIN ( SELECT * FROM names ORDER BY placeRef, incidence DESC ) AS p ON p.id = names.id,

2 - This makes a virtual table with the results ordered, so that rankings can be applied

( SELECT @curRank := 0, @nextRank := 0, @prevInc := 9999999999, @prevPlace := 0 ) AS v

3 - This set some variables that will be used to tell when to incrament and reset the rank

names.rank = IF(  @prevPlace != p.placeRef, @curRank := 0, 0 ),

4 - This is a hack that resets the current rank to 0 when MySQL iterates into a new place

names.rank = IF(  @prevPlace != p.placeRef, @nextRank := 0, 0 ),

5 - This is a hack that resets the next rank to 0 when MySQL iterates into a new place

names.rank = IF(  @prevInc = p.incidence, @nextRank := @nextRank + 1, @curRank := @nextRank := @nextRank + 1 ),

6 - This is a hack that updates the next and current ranks when the current incidence is the same as the previous incidence

names.rank = IF(  @prevInc = p.incidence, @curRank := @curRank, @curRank := @nextRank ),

7 - This sets the rank to the same as the last rank when its incidence is the same as the previous or increments the rank if it isn't

names.incidence = @prevInc := names.incidence,

8 - This is a hack that sets a variable to contain the previous incidence, so we can tell what to do in the next itteration

names.placeRef = @prevPlace := names.placeRef;

9 - This is a hack that sets a variable to contain the previous place, so we can tell what to do in the next itteration