Inserting a Series

[MaumeeScott]

I would like to be able to insert a series of numbers based on a field. What I essentially have is an ID# that may be repeated in the table. What I would like to do is update a separate column and insert a 1 for the first instance, 2 for the 2nd, etc. The data would look like the following when I am finished.

ID# Line#
A 1
A 2
A 3
B 1
C 1
C 2
C 3
D 1

I would like to use SQL to insert the line#. Any help or insights are greatly appreciated.

Scott

 

[jbenson001]

See this thread and vongrunt's post:
thread183-1158802

 

[vongrunt]

Deja vu? Not entirely but check it anyway: thread183-1158802

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Blah... you were faster

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[MaumeeScott]

That definitely helps, but how do I use that query with an update statement to acutally create the line#. Currently I only have the ID#. I am going to add the line# column and would like to populate with the serial data. Sorry to be so obtuse.

Scott

 

[jbenson001]

Try

'Create a SequenceNumber column in the table first
Update blah
SET SequenceNumber = count(*)
from blah A
inner join blah B on A.ClaimNumber = B.ClaimNumber and A.LineNumber >= B.LineNumber
group by A.ClaimNumber, A.LineNumber
order by A.ClaimNumber, A.LineNumber

p.s. I haven't tested this, so be sure to test on some test data.

Jim