title: MySQL: Update a "sort" column accoreding to ORDER BY
body: <p>Fully edited:
  My query has 2 columns from 2 tables: filename and sortorder. </p>
  
  <p>Full tables are done here <a href="http://sqlfiddle.com/#!9/8c0507" rel="nofollow">http://sqlfiddle.com/#!9/8c0507</a></p>
  
  <p>Example:</p>
  
  <pre><code>SELECT * FROM table1 t1
    LEFT JOIN table2 t2
      ON t1.idmd5 = t2.imgid
  ORDER BY t2.filename
  WHERE t1.anotherid = "123456";
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>filename-01.jpg   - 12
  filename-02.jpg   - 73
  filename-03.jpg   - 1
  filename-12.jpg   - 63
  filename-24.jpg   - 99
  </code></pre>
  
  <p>Now, the "sortorder" column should be re-set according to the ORDER BY - output, it should start at 1. </p>
  
  <p>Expected result:</p>
  
  <pre><code>filename-01.jpg   - 1
  filename-02.jpg   - 2
  filename-03.jpg   - 3
  filename-12.jpg   - 4
  filename-24.jpg   - 5
  </code></pre>
  
  <p>How to achieve that?</p>