Sequential Numbering and Counting of Records - Sequentially Numbering Records by Using a Self Join

(Page 4 of 6 )

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,
       a.LastName
     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 records counts produced by a self join can grows quite big when large sets are involved, causing the performance of this technique to have a 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.

Next: Sequentially Number Records by Using a Cursor >>
 

More MS SQL Server Articles
More By Gregory A. Larsen

Comments On: Sequential Numbering and Counting of Records

>>> Be the FIRST to comment on this article!