Search code examples
sql-serverusingauto-increment

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 1-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

Solution

  • 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