question

user-805 (google) avatar image
user-805 (google) asked

creating a cumulative counter with a cursor

My data looks like this:

 1. Name  Branch                     
 2. Ann   123                    
 3. Art   123                    
 4. Ed    123                     
 5. Sam   444                    
 6. Tim   444                    
 7. Tom   555                    

I want to set up a cumulative counter for each branch. When the branch changes, I want the counter to start over at 1. I want the data to look like this:

 1. Name  Branch Counter                    
 2. Ann   123    1                    
 3. Art   123    2                    
 4. Ed    123    3                    
 5. Sam   444    1                    
 6. Tim   444    2                    
 7. Tom   555    1                    

I have little experience with cursors, but I know I have to use them to accomplish this. Thanks in advance.

sql-server-2005t-sqlrownum
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
RickD avatar image
RickD answered

Why do you need a cursor?

SELECT             
    [Name],            
    Branch,            
    row_number() over (partition by Branch order by Branch, [Name]) as Counter            
FROM Table            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.