Add ranking variable to database with MySQL

June 2, 2022

I have a database with the following structure:

ID   GROUP   VALUE   RANK
2      1      999     0
1      1      888     0
3      2      777     0
4      2      111     0

I need to update the "RANK" column by ranking IDs according to the "VALUE" column and grouping by the "GROUP" column. Basically, in the example above, I should end up with:

ID   GROUP   VALUE   RANK
2      1      999     1
1      1      888     2
3      2      777     1
4      2      111     2

I tried following another answer and slightly changing the query:

SET @r=0;
UPDATE `table` SET `RANK` = @r:= (@r+1) ORDER BY `VALUE` DESC;

SET @r=0;
UPDATE `table` SET `RANK` = @r:= (@r+1) PARTITION BY `GROUP` ORDER BY `VALUE` DESC;

but this throws an error. Alternatively, this approach produces the correct ranking:

SELECT `ID`,
    RANK() OVER (PARTITION BY `GROUP` ORDER BY `VALUE`) as `RANK`
FROM table;

but it does not update the "RANK" column in the database. I slightly prefer the second solution to the first one, but how can I save the output of the query to the RANK column?

>Solution :

Join the table to your last query (also use DESC in the ORDER BY clause) in the UPDATE statement:

UPDATE tablename t
INNER JOIN (
  SELECT id, RANK() OVER (PARTITION BY `GROUP` ORDER BY `VALUE` DESC) AS `RANK`
  FROM tablename
) r ON r.ID = t.ID
SET t.`RANK` = r.`RANK`;