help with a cursor - 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

General SQL Server Forums

New to SQL Server Programming

help with a cursor

Author

Topic

p.harris
Starting Member

5 Posts

Posted - 2010-01-29 : 10:08:17

Hi

I have a sql2005 table with 3 columns
account, reference and sequence number

if account code on 2 lines is the same, as is the reference then the sequence number would increment by 1

IE

account reference sequence number
1234 bbbb 0
1234 cccc 0
1234 aaaa 0
1234 aaaa 1
1234 aaaa 2

and so on

Is there anyone who could tell me how to write a cursor to create the sequence numbers correctly please

many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-29 : 10:11:32

Try

select account, reference, row_number() over(partition by reference order by account,reference) as sequence_number from your_table

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-29 : 10:17:54

Not sure why you wanna strat from 0, but if you do then make small mod in the query like...

select account, reference, row_number() over(partition by reference order by account,reference) - 1 as sequence_number from your_table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-30 : 00:29:01

shouldnt it be this?

select account, reference, row_number() over(partition by reference,account order by reference) - 1 as sequence_number from your_table

p.harris
Starting Member

5 Posts

Posted - 2010-01-30 : 08:17:50

quote:

Originally posted by visakh16

shouldnt it be this?

select account, reference, row_number() over(partition by reference,account order by reference) - 1 as sequence_number from your_table

Many thanks
madhivanan had it exactly right

Once again many many thanks for helping me on this one

regards

Peter

p.harris
Starting Member

5 Posts

Posted - 2010-01-30 : 08:19:21

oooops sorry, my mistake it was visakh16 who was exactly right, but thanks to all, most helpful and useful

p.harris
Starting Member

5 Posts

Posted - 2010-01-31 : 02:47:55

I have a further problem with this script !!!!!!

The select stament works great and gives me the results I require, Ie the sequence_number correctly, but how do i now update the table with the results of the select ? however I try i get errors

Many thanks

Peter

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 00:44:55

quote:
Originally posted by p.harris

I have a further problem with this script !!!!!!

The select stament works great and gives me the results I require, Ie the sequence_number correctly, but how do i now update the table with the results of the select ? however I try i get errors

Many thanks

Peter

Dont try to update in the table. Use select statement only. If data are added again you need to update them

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 01:13:42

quote:
Originally posted by p.harris

oooops sorry, my mistake it was visakh16 who was exactly right, but thanks to all, most helpful and useful

No problem you're welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 01:17:05

quote:
Originally posted by p.harris

I have a further problem with this script !!!!!!

The select stament works great and gives me the results I require, Ie the sequence_number correctly, but how do i now update the table with the results of the select ? however I try i get errors

Many thanks

Peter

whats it that you're trying to update? the newly generated seq number?

p.harris
Starting Member

5 Posts

Posted - 2010-02-02 : 12:36:54

I have a table where the sequence number field is presently blank. using the rules as above i wish to populate the sequence number field with the results of the script that visakh16 and others so kindly provided above. I would be grateful if anyone could give me a scriot or even a cursor to achieve this

Many thanks

Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 13:30:21

just use like


update t 
set t.seq=sequence_number
from
(
select account, reference,seq, row_number() over(partition by reference,account order by reference) - 1 as sequence_number from your_table
)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources