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.
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