Incremental count column - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Incremental count column

Author

Topic

dlhall
Starting Member

8 Posts

Posted - 2009-07-23 : 11:22:13

I need to populate a column with the running count based on another column contents. The desired outcome table is like this:

count seq_num
1 123-456-789
1 123-456-780
1 123-456-990
2 123-456-789
2 123-456-990

I have the seq_num column populated, but can't get the proper syntax to populate the count column based in seq_num contents

So, as the seq_num column changes, the counter resets to '1' and as the column repeats, the counter increments by 1.

This is using SQL2000, and the seq_num field is varchar.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 11:56:11

time to go to 2008 because there it is no problem.

sorry

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-23 : 20:55:10

[code]
declare @seq_num varchar(12)

select @seq_num = '123-456-789'

select stuff(
stuff(
convert(varchar(12),
convert(int,
replace(@seq_num, '-', '')
) + 1
), 4, 0, '-'
), 8, 0, '-'
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-24 : 01:27:06

Hey KH,
it is fine increment the seq_num but the problem is that the OP wants something like row_number().

Greetings to Singapore
Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 01:59:56

quote:
Originally posted by webfred

Hey KH,
it is fine increment the seq_num but the problem is that the OP wants something like row_number().

Greetings to Singapore
Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Oh I had totally read it wrongly

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 02:29:46

do you have a Primary Key in the table ?


select	seq_num, cnt = (select count(*) from table x where x.seq_num = t.seq_num and x.pk <= t.pk) 
from	table t
order by cnt, seq_num

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-24 : 03:18:34

May be point 3
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

dlhall
Starting Member

8 Posts

Posted - 2009-07-24 : 10:57:55

Thanks to all. In answer to khtan's question, the seq_num is the PK for the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources