Sequential line number (counter) based on changing value within group 1

[xcaliber2222]

Hello, Not sure what the best approach is, but could someone please show me how I could select/loop through and assign line numbers to values within a resultset like this:


[name]

       [id]
Base Rate	              BAS	  2510162
Delivery Mileage	     260	  2510162
Pickup Mileage	             540	  2510162
Security Surcharge	     GSS	  2510162
Fuel Surcharge	             405	  2510162
Base Rate	               BAS	  2510384
AFTER HOURS 	             685	  2510384
Pickup Mileage	             540	  2510384
Security Surcharge	     GSS	  2510384
Fuel Surcharge	             405	  2510384

So, for each [id] group I would have line numbers for its values like this:

[line#] [name]                     [code]     [id]
1       Base Rate	             BAS	    2510162
2       Delivery Mileage	    260	    2510162
3       Pickup Mileage              540	  2510162
4       Security Surcharge	    GSS	  2510162
5       Fuel Surcharge	            405	  2510162
1       Base Rate	            BAS	  2510384
2       AFTER HOURS   	            685	  2510384
3       Pickup Mileage	            540	  2510384
4       Security Surcharge	    GSS	  2510384
5       Fuel Surcharge	            405	  2510384

Thank you for your help,
Alejandro

 

[gmmastros]

What version of SQL Server? If you are using SQL 2005 or above, you can use Row_Number with a partition by. In SQL2000, you can accomplish this with table variables, but it's a bit more tricky.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[xcaliber2222]

Hi George,

Sorry, I should have said this before. SQL Server 2000.

Thank you,
Alejandro

 

[gmmastros]

Alejandro,

First, realize that the code I show below first creates a table variable named @Original and then add your test data to it. This means you can copy/paste this code to a query window to see how it runs. If you are satisfied that it returns the correct results, then you should remove the @Original table variable and the code that inserts data in to it. Then, modify the code below to use your actual table.

[green]-- Table variable to simulate data[/green]
Declare @Original Table(Name VarChar(40), Code VarChar(3), Id Int)

Insert Into @Original Values('Base Rate          ','BAS',      2510162)
Insert Into @Original Values('Delivery Mileage   ','260',      2510162)
Insert Into @Original Values('Pickup Mileage     ','540',      2510162)
Insert Into @Original Values('Security Surcharge ','GSS',      2510162)
Insert Into @Original Values('Fuel Surcharge     ','405',      2510162)
Insert Into @Original Values('Base Rate          ','BAS',      2510384)
Insert Into @Original Values('AFTER HOURS        ','685',      2510384)
Insert Into @Original Values('Pickup Mileage     ','540',      2510384)
Insert Into @Original Values('Security Surcharge ','GSS',      2510384)
Insert Into @Original Values('Fuel Surcharge     ','405',      2510384)

[green]-- Actual code starts here[/green]


Declare @Temp Table(RowId Int Identity(1,1), name VarChar(40), Code VarChar(3), Id int)

Insert Into @Temp(Name, Code, Id)
Select Name, Code, Id
From   @Original
Order By Id

Select RowId - MinRowId + 1 As LineNumber, T.Name, T.Code, T.Id
From   @Temp T
       Inner Join (
         Select id, Min(RowId) As MinRowId
         From   @Temp
         Group By Id
         ) As A
         On T.Id = A.Id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[xcaliber2222]

Thanks George! Perfect!