Geek asks and answers

Mysql sort field increment value

Say I have a table

name             rank
-----------------------
John             1
Tit              3
Bernard          4

Rank 2 is missing, can be removed or whatever. I need a query to increase the rank field. So John will be number 1, but now Titus will be number 2, and Bernard 3.

There can be up to 100 ranks, and some are missing. So far, the lowest rank is reset to number 1, and everything that follows the step should be good.

Any ideas?

Request to update the rank field.

+5
source share
2 answers

This will update the rank field so that it grows without holes:

SET @i := 0;
UPDATE tbl SET rank = @i:=@i+1 ORDER BY rank;
+6
source

You can do this without using external variables:

UPDATE tbl a
INNER JOIN
(
    SELECT a.name, a.rank, COUNT(*) AS newrank
    FROM tbl a
    INNER JOIN tbl b ON a.rank >= b.rank
    GROUP BY a.rank
) b ON a.name = b.name AND a.rank = b.rank
SET a.rank = b.newrank
+2
source

More articles:


All Articles