title: MySQL get row position with ORDER BY incorrectly numbered
body: <p>I have the following mySQL query which I want to give the rows sorted on a calculated field with the corresponding row position. I've used OMG Ponies code from <a href="https://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by">here</a> as a template but the rows are coming back incorrectly numbered (they are being numbered in key (gemid) order without the sort). I know it has to do with the JOIN on a GROUP BY but I know know how to fix it. Thanks in advance.</p>
  
  <pre><code>SELECT g.gemid, sum_rating, @rownum := @rownum + 1 AS row_num FROM gems g
      LEFT JOIN (SELECT gemid, SUM(rating) as sum_rating from gemrating GROUP BY gemid) rt ON g.gemid = rt.gemid 
      JOIN (SELECT @rownum := 0) Z 
      WHERE g.grade = '8'  
      ORDER BY sum_rating asc
  </code></pre>
  
  <p>The output should come back looking like:</p>
  
  <pre><code>gemid    sum_rating    row_num
  ------   ------------  ----------
  2           10           1
  4           25           2
  1           40           3
  3           41           4
  </code></pre>
  
  <p>Instead it is coming back:</p>
  
  <pre><code>gemid    sum_rating    row_num
  ------   ------------  ----------
  2           10           2
  4           25           4
  1           40           1
  3           41           3
  </code></pre>