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.