Incrementing values in SQL Server using programming

I have a table in SQL Server which has 3 fields,

Id int identity(1,1)
thRead int
level int

Now in this table there are threads, like 开发者_如何学Go1-5, all are repeating 5times, like this:

 1,1,1,1,1 2,2,2,2,2 3,3,3,3,3 4,4,4,4,4 5,5,5,5,5

Now I want to update level such that, for a group of records it should increment starting from zero, for another group again from 0 and so on..

I want the output like table below....

1   0
1   1
1   2
1   3
1   4
2   0
2   1
2   2
2   3
2   4
3   0
3   1
3   2
3   3
3   4

Please can anyone help me out with this... the update should be with select query so no need to enter thread manually, it should update automatically

Thanks and Regards Abbas Electricwala

---

Is this a one off update? If so this would work.

with cte as
(
SELECT Id , thRead, level,
       ROW_NUMBER() OVER (PARTITION BY thRead ORDER BY Id) -1 AS RN
FROM T
)
UPDATE cte
SET level = RN

If you want to do this ongoing for new rows that is more difficult.

---

You can try this

;WITH Temp as
(
 SELECT Id , thRead, ROW_NUMBER() OVER (PARTITION BY thRead ORDER BY Id) -1 'Level' 
 FROM YourTable
)
Select Id, ThRead, Level from Temp