title: How to increment in a select query
body: <p>I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different. </p>
  
  <p>I know this code doesn't work. Programmatically this is what I want...</p>
  
  <pre><code>declare @counter int, @id
  set @counter = 0
  set @id = 0
  
  select distinct 
    id, 
    counter = when id = @id 
                then @counter += 1
              else @id = id  
                 @counter = 1     
  </code></pre>
  
  <p>...with the end result looking something like this:</p>
  
  <pre><code>ID    Counter
  3     1
  3     2 
  3     3
  3     4
  6     1
  6     2
  6     3
  7     1
  </code></pre>
  
  <p>And yes, I am stuck with SQL2k. Otherwise that row_number() would work.</p>