Mysql update max value with group by

Question

I have a simple table that has many rows already:

id  grade  ...
1    1     ...
2    2     ...
3    2     ...
4    1     ...
5    1     ...

Now i want to add a column "relative_order", which is the order in that grade. So the table becomes:

id  grade  ...  relative_order
1    1     ...       1
2    2     ...       1
3    2     ...       2
4    1     ...       2
5    1     ...       3

After i add the column, all the relative_order becomes 0 first. How can i fill the relative_order column using update syntax?

I tried using inner join, but failed:

UPDATE table AS i
INNER JOIN(
  SELECT max(relative_order) as mOrder,grade
  FROM table 
  GROUP BY grade
) AS j
ON i.grade = j.grade
SET i.relative_order = j.mOrder + 1

Answer 1

You could use this SELECT query that will return the relative_order that you need:

SELECT
  t1.id,
  t1.grade,
  COUNT(t2.id) relative_order
FROM
  yourtable t1 INNER JOIN yourtable t2
  ON t1.grade=t2.grade AND t1.id>=t2.id
GROUP BY
  t1.id,
  t1.grade

or if you want to update the value, you can join your table with the previous query, like this:

UPDATE
  yourtable INNER JOIN (
    SELECT
      t1.id,
      t1.grade,
      COUNT(t2.id) relative_order
    FROM
      yourtable t1 INNER JOIN yourtable t2
      ON t1.grade=t2.grade AND t1.id>=t2.id
    GROUP BY
      t1.id,
      t1.grade) seq
  ON yourtable.id=seq.id AND yourtable.grade=seq.grade
SET
  yourtable.relative_order = seq.relative_order

Please see fiddle here.