How to increment in a select query

Question

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.

I know this code doesn't work. Programmatically this is what I want...

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     

...with the end result looking something like this:

ID    Counter
3     1
3     2 
3     3
3     4
6     1
6     2
6     3
7     1

And yes, I am stuck with SQL2k. Otherwise that row_number() would work.

Answer 1

Assuming a table:

CREATE TABLE [SomeTable] (
  [id] INTEGER,
  [order] INTEGER,
  PRIMARY KEY ([id], [order])
);

One way to get this in Microsoft SQL Server 2000 is to use a subquery to count the rows with the same id and a lower ordering.

SELECT *, (SELECT COUNT(*) FROM [SomeTable] counter 
           WHERE t.id = counter.id AND t.order < counter.order) AS row_num
FROM [SomeTable] t

Tip: It's 2010. Soon your SQL Server will be old enough to drive.

If you use SQL Server 2005 or later, you get wonderful new functions like ROW_NUMBER() OVER (PARTITION...).