incrementing number based on value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

incrementing number based on value

Author

Topic

richcol
Starting Member

5 Posts

Posted - 2007-03-21 : 13:08:49

Hi All,
I am having a problem trying to add a sequence of numbers to my test table and it is a bit confusing. Could anyone point me in the right direction.

I have the folowing

TestTable
PK Clm Row
1 123 0
2 123 0
3 123 0
4 234 0
5 234 0
6 345 0

what i am trying to do is apply row numbers based on the clm column so it looks like the following:

PK Clm Row
1 123 1
2 123 2
3 123 3
4 234 1
5 234 2
6 345 1

Hope this makes sense.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-21 : 13:23:19

update tbl
set row = (select count(*) from tbl t2 where t2.Clm = t.Clm and t2.Pk <= t.Pk)
from tbl t

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

richcol
Starting Member

5 Posts

Posted - 2007-03-22 : 09:27:46

That's great exactly what I was looking for, many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources