title: Mysql update max value with group by
body: <p>I have a simple table that has many rows already:</p>
  
  <pre><code>id  grade  ...
  1    1     ...
  2    2     ...
  3    2     ...
  4    1     ...
  5    1     ...
  </code></pre>
  
  <p>Now i want to add a column <code>"relative_order"</code>, which is the order <strong>in that grade</strong>. So the table becomes:</p>
  
  <blockquote>
  <pre><code>id  grade  ...  relative_order
  1    1     ...       1
  2    2     ...       1
  3    2     ...       2
  4    1     ...       2
  5    1     ...       3
  </code></pre>
  </blockquote>
  
  <p>After i add the column, all the relative_order becomes 0 first. How can i fill the relative_order column using <code>update</code> syntax?</p>
  
  <p>I tried using <strong>inner join</strong>, but failed:</p>
  
  <pre><code>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
  </code></pre>