Incrementing values in SQL Server using programming

Question

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

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

How exactly the output is relevent to your expected result? Can you explain more? — Anil Soman
– Anil Soman, Commented Feb 4, 2011 at 10:30
@Anil: actually for any thread added in the forum, its post level increements and when another thread starts the post level is again set to 0, so for any threads the post level should increement, till the thread ends and new thread is start... — Abbas
– Abbas, Commented Feb 4, 2011 at 10:41

Martin Smith · Accepted Answer · 2011-02-04 10:40:13Z

1

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.

answered Feb 4, 2011 at 10:40

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anil Soman · Accepted Answer · 2011-02-04 11:06:23Z

0

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

answered Feb 4, 2011 at 11:06

Anil Soman

2,4677 gold badges41 silver badges68 bronze badges

3 Comments

Abbas Over a year ago

great help man... it worked... thanks a lot anil, but if you can, please explain how does that work out... i didnt understood the logic for it.

Anil Soman Over a year ago

@Aabas - Good to know ... The first part in the query is CTS - common table expression. This gives you a temporary set of data which you can use in your main query. Much like table created in FROM Clause. In this CTS I have used ROW_Number() which is SQL in-build function which gives index value to the field you mention in OVER part's PARTITION clause. The result is ordered for each group by ORDER BY clause. Hope this clarifies your idea.

Anil Soman Over a year ago

@Aabas - Also, you can mark my answer as accepted answer if it has really solved your problem :)

Collectives™ on Stack Overflow

Incrementing values in SQL Server using programming

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related