Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sequential Numbering

By Gregory Larsen,

Microsoft SQL server does not support a method of identifying the row numbers for records stored on disk, although there are a number of different techniques to associate a sequential number with a row. You might want to display a set of records might where each record is listed with a generated number that identifies the records position relative to the rest of the records in the set. The numbers might be sequential that start at 1 and are incremented by 1 for each following record, like 1,2,3,4, etc.. Or in other case you may want to sequentially number groupings of records where each specific set of records are numbered starting at 1 and incremented by 1 until the next set is reach where the sequence starts over. This article will show a number of different methods of assigning a record sequence number to records returned from a query.

Sequentially Numbering Records By Having an Identity Column

Even though Microsoft SQL Server does not physically have a row number stored with each record, you can include one of your own. To have your own record number, all you need to do is include an identity column in your table definition. When you define the identity column you can specify an initial seed value of 1, and a increment value of 1. By doing this the identity column will sequentially number each row inserted into the table. Let me show you a simple CREATE TABLE statement that defines a ROW_NUMBER column, which will sequentially number records.

SET NOCOUNT ON
CREATE TABLE SEQ_NUMBER_EXAMPLE (
RECORD_NUMBER INT IDENTITY (1,1),
DESCRIPTION VARCHAR(40))
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FIRST RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('SECOND RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('THIRD RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FOURTH RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FIFTH RECORD')
SELECT * FROM SEQ_NUMBER_EXAMPLE
DROP TABLE SEQ_NUMBER_EXAMPLE

When you run this code it produces the following output:

RECORD_NUMBER DESCRIPTION 
------------- ---------------------------------------- 
1 FIRST RECORD
2 SECOND RECORD
3 THIRD RECORD
4 FOURTH RECORD
5 FIFTH RECORD

Now as you can see, each record has been automatically numbered using the identity column RECORD_NUMBER. One thing to consider when using this method is that there is no guarantee that these numbers are physically stored next to each other on disk, unless there is a clustered index on the RECORD_NUMBER column. If you use this method either create a clustered index, or have an ORDER BY RECORD_NUMBER clause to ensure that the records are returned in sequential order. Also remember if you should delete records, then your sequential number will have missing values for each record deleted.

Sequentially Numbering Records by Using a Temporary Table

Now you might not have designed your table to have an identity column, or even want to place one on your existing table, so another option is to insert the records you desired to have a sequence number into a temporary table. Here is some code that takes the Northwind.dbo.Employees table and copies only the Sales Representatives into a temporary table. This example uses this temporary table with a rank identity column to show a ranking of Sales Representatives by HireDate.

create table #HireDate (rank int identity, 
HireDate datetime,
LastName nvarchar(20),
FirstName nvarchar(20)
)

insert into #HireDate (HireDate, LastName, FirstName)
select Hiredate, LastName, Firstname
from northwind.dbo.employees
where Title = 'Sales Representative'
order by HireDate

Select cast(rank as char(4)) as Rank, 
cast(hiredate as varchar(23)) as HireDate,
LastName, 
FirstName from #HireDate

Drop table #HireDate

The output of this example looks like this:

Rank HireDate LastName FirstName 
---- ----------------------- -------------------- --------------- 
1 Apr 1 1992 12:00AM Leverling Janet
2 May 1 1992 12:00AM Davolio Nancy
3 May 3 1993 12:00AM Peacock Margaret
4 Oct 17 1993 12:00AM Suyama Michael
5 Jan 2 1994 12:00AM King Robert
6 Nov 15 1994 12:00AM Dodsworth Anne

Sequentially Numbering Records by Altering Table

Ok, so you don’t want to create a temporary table, but instead you want to use the existing table to identify the row numbers for each record. You can still do this provided you don’t have a problem with altering the table. To have row numbers, all you need to do is alter the table to add an identity column with a initial seed value of 1 and an increment of 1. This will number your rows from 1 to N where N is the number of rows in the table. Lets look at an example of this method using the pub.dbo.titles table.

set nocount on
alter table pubs.dbo.titles
add rownum int identity(1,1)
go
select rownum, title from pubs.dbo.titles
where rownum < 6
order by rownum
go
alter table pubs.dbo.titles
drop column rownum

Note this example first alters the table, then displays the first 5 rows, and lastly drops the identity column. This way the row numbers are produced, displayed and finally removed, so in effect the table is left as it was prior to running the script. The output from the above script would look like this.

rownum title 
----------- ---------------------------------------------------------------- 
1 But Is It User Friendly?
2 Computer Phobic AND Non-Phobic Individuals: Behavior Variations
3 Cooking with Computers: Surreptitious Balance Sheets
4 Emotional Security: A New Algorithm
5 Fifty Years in Buckingham Palace Kitchens

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,
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 good 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.

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
begin
set @i = @i + 1
print cast(@i as char(7)) + rtrim(@name)
fetch next from authors_cursor into @name
end
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, lets 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 ranges 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
join 
(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

Conclusion

These examples represent a number of different approaches at sequentially numbering a sets for records. None of these methods are perfect. But hopefully these methods will give you some ideas on how you might be able to tackle your sequential record numbering issues

Total article views: 10794 | Views in the last 30 days: 0
 
Related Articles
ARTICLE

Sequential Ordering on SQL 2005

Many people have posed the question of how to generate sequential numbers for some report when the u...

FORUM

Order by numbers in proper sequence

Ordering by numbers in proper sequence order

FORUM

Match up sequential records

Match up 'in' and 'out' logged times in sequential data

FORUM

Sequential Numbering

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

FORUM

Inserting Multiple Records Using Range Numbers

Inserting Multiple Records Using Range Numbers

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones