Generate sequential numbers within a grouping in SQL Server

This article will discuss how to generate sequential numbers within a grouping in a SQL server table. I will say right up front, I know that set based operations are best and that any kind of cursor or looping should be avoided if possible. I have not been able to come up with a solution based purely on sets. This solution does not involve a cursor but it does involve a loop. Also, the data is being stored in a table variable so that limits what you can do. Having said all that, here is what I set out to accomplish. Suppose you have the following table structure.

Declare @Tab Table
(
  ID UniqueIdentifier not null default NewID(),
  Parent int not null,
  Description char(20),
  GrpRowCounter int
)

The ID uniquely identifies eac record. The Parent field is the field we are interested in grouping. The Description is there for flavor and the GrpRowCounter should be the value 1,2,3, etc... within each Parent grouping. Now that we have the table, let's load it up with sample data.

Insert Into @Tab (Parent, Description) Values (10,'ABC')
Insert Into @Tab (Parent, Description) Values (10,'DEF')
Insert Into @Tab (Parent, Description) Values (30,'GHI')
Insert Into @Tab (Parent, Description) Values (30,'JKL')
Insert Into @Tab (Parent, Description) Values (22,'MNO')
Insert Into @Tab (Parent, Description) Values (30,'PQR')
Insert Into @Tab (Parent, Description) Values (30,'STU')
Insert Into @Tab (Parent, Description) Values (10,'VWX')
Insert Into @Tab (Parent, Description) Values (22,'YZ1')
Insert Into @Tab (Parent, Description) Values (30,'234')

Notice that we did not populate the GrpRowCounter field just yet. The next step is to populate the table with that information. Since we are using a table variable, I selected to go with a loop structure. This loop structure will visit each of the rows in order and add the GrpRowCounter. For the code, we are going to follow this basic flow.

1. Visit each of the rows in order.
2. If the Parent code changes, reset our GrpRowCounter variable.
3. If the Parent code does not change, increment our GrpRowCounter.
4. Update the table.

Here is the code to do it.

Declare @ID uniqueidentifier  -- ID we are currently working with.
Declare @Parent int           -- parent we are currently working with.

Declare @TotalRows int      -- This holds the total number of rows in the table.
Declare @RowCounter int     -- This is my counter variable.
Declare @LastParent int     -- This holds the last looked up parent.  When a parent 
                            -- changes, the GroupRowCounter Resets
Declare @GrpRowCounter int  -- this holds the 1,2,3 per group.

-- this gets our maximum number of records so we know when to stop.
Select @TotalRows = COUNT(*) From @Tab   
-- Counter is initialized to 1.
Set @RowCounter = 1                      

While @RowCounter <= @TotalRows
Begin
    -- Get the current record.  We are using the Row_Number function to match the corect
    -- row back to the appropriate ID.  Also, notice we order on Parent first and then 
    -- on ID.
    Select @ID = T.ID, @Parent = T.Parent From @Tab T
    Inner Join (Select ID, ROW_NUMBER() Over (Order by Parent, ID) RowNum From @Tab) SubT
    On T.ID = SubT.ID
    Where SubT.RowNum = @RowCounter
    Order by Parent, T.ID
    
    -- Check to see if the Parent has changed.  On the first record, @LastParent 
    -- is null so it should fail and set the grp counter to 1
    IF @Parent = @LastParent 
        Set @GrpRowCounter = @GrpRowCounter + 1
    Else
        Set @GrpRowCounter = 1
    
    -- Now update the Record
    Update @Tab Set GrpRowCounter = @GrpRowCounter
    Where ID = @ID
    
    -- Now update the last parent
    Set @LastParent = @Parent
    
    -- Finally update our overall counter.
    Set @RowCounter = @RowCounter + 1
End

-- See the results
Select * From @Tab Order by Parent, ID