Sequentially Numbering Records by Using a
Now say your table does not
have an identify column, you don't want to use a temporary table or alter your
existing table, but you still would like to have a record number associated
with each record. In this case you could use a self join to return a record
number for each row. Here is an example that calculates a RecNum column, and
displays the LastName for each record in the Northwind.dbo.Employees table.
This example uses count(*) to count the number of records that are greater than
or equal LastName in this self join.
SELECT count(*) RecNum,
FROM Northwind.dbo.Employees a join
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
The results from this query
looks like this:
This method works well for a
small number of records, a few hundred or less. Since the number of record counts
produced by a self join can grow quite big when large sets are involved, it can
cause the performance of this technique to have slow response times for large
set. This method also does not work if there are duplicate values in the
columns used in the self join. If there are duplicates then the RecNum column
will contain missing values.
Sequentially Number Records by Using a
A cursor can be used to
associate a sequential number with records. To use this method you would
allocate a cursor, then process through each cursor record one at a time
associating a record number with each record. Here is an example that does
just that. This example displays the authors last and first name with a
calculated recnum value for each author in the pubs.dbo.authors table where the
authors last name is less than 'G'. Each author is displayed in order by last
name and first name with the first author alphabetically being assigned a recnum
of 1, and for each successive author the recnum is incremented by one.
declare @i int
declare @name varchar(200)
declare authors_cursor cursor
for select rtrim(au_lname) + ', ' + rtrim(au_fname) from pubs.dbo.authors
where au_lname < 'G'
order by au_lname, au_fname
fetch next from authors_cursor into @name
set @i = 0
print 'recnum name'
print '------ -------------------------------'
while @@fetch_status = 0
set @i = @i + 1
print cast(@i as char(7)) + rtrim(@name)
fetch next from authors_cursor into @name
Output from the cursor query
looks like this.
1 Bennet, Abraham
2 Blotchet-Halls, Reginald
3 Carson, Cheryl
4 DeFrance, Michel
5 del Castillo, Innes
6 Dull, Ann
Sequentially Numbering Groups of Records
Another case I have run
across for sequentially number records is where you want to number groups of
records. Where each group starts numbering from 1 to N, where N is the number
of records in the group, and then starts over again from 1, when the next group
For an example of what I am
talking about, let's say you have a set of order detail records for different
orders, where you want to associate a line number with each order detailed
record. The line number will range from 1 to N, where N is the number of order
detail records per order. The following code produces line numbers for orders
in the Northwind Order Detail table.
select OD.OrderID, LineNumber, OD.ProductID, UnitPrice, Quantity, Discount
from Northwind.dbo.[Order Details] OD
(select count(*) LineNumber,
from Northwind.dbo.[Order Details] A join
Northwind.dbo.[Order Details] B
on A.ProductID >= B.ProductID
and A.OrderID = B.OrderID
group by A.OrderID, A.ProductID) N
on OD.OrderID= N.OrderID and
OD.ProductID = N.ProductID
where OD.OrderID < 10251
order by OD.OrderID, OD.ProductID
This code is similar to the prior
self join example, except this code calculates the LineNumber as part of a subquery.
This way the LineNumber calculated in the subquery can be joined with the
complete Order Detail record.
The above query produces the
OrderID LineNumber ProductID UnitPrice Quantity Discount
----------- ----------- ----------- --------------------- -------- ---------------
10248 1 11 14.0000 12 0.0
10248 2 42 9.8000 10 0.0
10248 3 72 34.8000 5 0.0
10249 1 14 18.6000 9 0.0
10249 2 51 42.4000 40 0.0
10250 1 41 7.7000 10 0.0
10250 2 51 42.4000 35 0.15000001
10250 3 65 16.8000 15 0.15000001
These examples represent a
number of different approaches at sequentially numbering sets for records.
None of these methods are perfect. However, I hope these methods will give you
some ideas on how you might be able to tackle your sequential record numbering
See All Articles by Columnist Gregory A. Larsen