creating a cumulative counter with a cursor

Question

question

user-805 (google) asked Feb 5, '10

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-2005 t-sql rownum

10 |1200

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

Answer 1 · 2010-02-05T12:25:13Z

RickD answered Feb 5, '10

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

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

question