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.

Solution 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...).

Solution 2

Yes you want ROW_NUMBER().

I would try:

SELECT id, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Counter

Solution 3

One way to do this is to throw the data into a temp table with an identity column that is used as a row number. Then make the counter column a count of the other rows with the same Id and a lower row number + 1.

CREATE TABLE #MyData(
Id INT
);

INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(3);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(6);
INSERT INTO #MyData VALUES(7);

CREATE TABLE #MyTempTable(
RowNum INT IDENTITY(1,1),
Id INT,
Counter INT
);

INSERT INTO #MyTempTable
SELECT Id, 0
FROM #MyData
ORDER BY Id;

SELECT Id, (SELECT COUNT(*) + 1 FROM #MyTempTable WHERE Id = t1.Id AND RowNum < t1.RowNum) AS 'Counter'
FROM #MyTempTable t1;

You should get the following output based on your example:

Id  Counter
3   1
3   2
3   3
3   4
6   1
6   2
6   3
7   1