Add ranking variable to database with MySQL-CodePudding

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?

CodePudding user response：

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`;