title: SQL Server : Identity column by group
body: <p>How can I add an identity number so that when a row is inserted an incremental number is assigned as below by a trigger? I am using SQL Server.</p>
  
  <pre><code>1    AAA  
  2    AAA  
  3    BBB  
  4    CCC  
  5    CCC  
  6    CCC  
  7    DDD  
  8    DDD  
  9    EEE  
  ....
  </code></pre>
  
  <p>And I want to convert it to:</p>
  
  <pre><code>1    AAA   1
  2    AAA   2
  4    CCC   1
  5    CCC   2
  6    CCC   3
  7    DDD   1
  8    DDD   2
  </code></pre>