Updating a column based on its position in the results

Question

I have a query that loops through each result and updates a column:

SET @counter = 0;
UPDATE users SET rank_level = @counter := @counter + 1 ORDER BY level DESC;
SELECT rank_level, level FROM users ORDER BY rank_level ASC;

Which outputs:

But what I am trying to do is only increment the variable if the level value changes. So where the two rows that have the same level are, they would have the same rank of 8 too.

Any ideas? Thanks.

The second answer to the question I marked as duplicate holds the answer. It's basically the MySQL version of the windowed rank() function. — jpw, Nov 25 '15 at 23:18
I'm not positive this would be considered a duplicate -- seen a lot worse questions with similar answers on SO. This should help: http://sqlfiddle.com/#!9/3e981/1 — sgeddes, Nov 25 '15 at 23:25
@sgeddes If you truly believe that this question isn't the same and that the answer I indicated isn't applicable you do have the power to reopen the question. I believe my assessment was correct though. — jpw, Nov 25 '15 at 23:49
It is slightly different as the other is a select, where mine is an update, but same logic. — 472084, Nov 25 '15 at 23:51
I re-opened the question because the two leading answers on the other questions implemented `row_number()` and `dense_rank()`, but not `rank()`. — Gordon Linoff, Nov 26 '15 at 01:36

score 0 · Answer 1 · answered Nov 26 '15 at 01:39

rank() is a bit tricky in MySQL. One way is with a correlated subquery:

select u.*,
       (select count(*) + 1
        from users u2
        where u2.level < u.level
       ) as rank
from users u;

This is tricky to put into an update. Assuming you have a userId column, you can use join:

update users u join
       (select u.*,
               (select count(*) + 1
                from users u2
                where u2.level < u.level
               ) as rank
        from users u
       ) ur
       on u.userId = ur.userId
    set rank_level = rank;

Doing a rank with variables is rather tricky (row_number() and dense_rank() are easier), because it requires three variables. Here is the select version:

select u.*,
       (@r := if(@l = level,
                 if(@rn := @rn + 1, @r, @r)
                 if(@l := level,
                    @rn := @rn + 1, @rn := @rn + 1
                   )
                )
       ) as ranking
from users u cross join
     (select @l := -1, @rn : = 0, @r := 0) params
order by level;

score 0 · Accepted Answer · edited May 23 '17 at 12:23

0

So in the end I went with this:

SET @prev_value = NULL;
SET @rank_count = 0;

UPDATE users SET rank_level = CASE
  WHEN @prev_value = level THEN @rank_count
  WHEN @prev_value := level THEN @rank_count := @rank_count + 1
END
ORDER BY level DESC;

Based on this answer: Rank function in MySQL

edited May 23 '17 at 12:23

Community

1
1

answered Nov 26 '15 at 11:12

472084

17,666
10
63
81

Updating a column based on its position in the results

2 Answers2