Increment a counter by User - 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 2005 Forums

Transact-SQL (2005)

Increment a counter by User

Author

Topic

bmatthews
Starting Member

8 Posts

Posted - 2009-03-19 : 03:23:10

Greetings! Im new here and fairly new to SQL, but im happy to be here at SQLTeam and hope I can help out folks here as well! I seem to spend almost all my days working with it anymore anyway!

In any case, I am trying to import a user by ID. (I shortened it down a bit for practical purposes). In the spreadsheet I have multiple records that contain the same ID, but have a different friend. I need to make a counter that gives a unique ID (called sequence) for each friend. I think I will need to use an 'IF EXISTS' statement? Also, the sequence number needs to reset to 0 when I get to a new user ID.

For instance

ID, Friend, Sequence
1, 1, 1
1, 2, 2
1, 3, 3
2, 1, 1,
2, 2, 2

etc.

declare
@ID nvarchar(4)
@Friend nvarchar(20)
@Sequence nvarchar(2)
SET @Sequence = 0
declare id_cursor cursor

FOR SELECT DISTINCT ID, Friend
FROM Spreadsheet

open id_cursor
Fetch next from id_cursor into
@ID, @Friend

IF @@FETCH_STATUS <> 0
PRINT 'No record types'

WHILE @@FETCH_STATUS = 0
Begin -- Fetch

insert into dbo.table
(id, friend, sequence)
values
(@ID, @Friend, @Sequence)

Fetch next from id_cursor into
@ID, @Friend

SET @Sequence = Sequence + 1
End -- Fetch Process_requests

Close id_cursor
Deallocate id_cursor

matty
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 04:01:31

If u set sequence as IDENTITY column all the rows in the table will have a unique ID

bmatthews
Starting Member

8 Posts

Posted - 2009-03-19 : 11:20:11

Im sorry, but the sequence number needs to reset to 0 when I get to a new ID.

For instance

ID, Friend, Sequence
1, 1, 1
1, 2, 2
1, 3, 3
2, 1, 1,
2, 2, 2

etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 11:26:39

seeme like this


SELECT ID,Friend,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Friend) AS Sequence
FROM YourTable

bmatthews
Starting Member

8 Posts

Posted - 2009-03-19 : 12:10:05

[code]SELECT ID,Friend,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Friend) AS Sequence
FROM YourTable[/code]

That worked! Thank you so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 12:43:44

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources