title: MySQL: Add sequence column based on another field
body: <p>I'm working on some legacy code/database, and need to add a field to the database which will record a sequence number related to that (foreign) id.</p>
  
  <p>Example table data (current):</p>
  
  <pre><code>ID     ACCOUNT     some_other_stuff
  1      1           ...
  2      1           ...
  3      1           ...
  4      2           ...
  5      2           ...
  6      1           ...
  </code></pre>
  
  <p>I need to add a sequenceid column which increments separately for each account, achieving:</p>
  
  <pre><code>ID     ACCOUNT     SEQ     some_other_stuff
  1      1           1       ...
  2      1           2       ...
  3      1           3       ...
  4      2           1       ...
  5      2           2       ...
  6      1           4       ...
  </code></pre>
  
  <p>Note that the sequence is related to account.</p>
  
  <p>Is there a way I can achieve this in SQL, or do I resort to a PHP script to do the job for me?</p>
  
  <p>TIA,
  Kev</p>