Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 6, 2003

Sequential Numbering/Counting of Records with SQL Server - Page 2

By Gregory A. Larsen

Sequentially Numbering Records by Using a Self Join

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
          Northwind.dbo.Employees b
          on a.LastName >= b.LastName
     group by a.LastName
     order by a.LastName

The results from this query looks like this:

RecNum      LastName             
----------- -------------------- 
1           Buchanan
2           Callahan
3           Davolio
4           Dodsworth
5           Fuller
6           King
7           Leverling
8           Peacock
9           Suyama

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 Cursor

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
open authors_cursor
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
close authors_cursor
deallocate authors_cursor

Output from the cursor query looks like this.

RecNum Name
------ -------------------------------
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 is encountered.

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, 
                a.OrderID, a.ProductID
                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 following output:

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 issues.

» See All Articles by Columnist Gregory A. Larsen

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM