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. Today, we'll cover how you can number records by having an identity column, using a temporary table, altering tables, using Self Join, usiung a cursor, and numbering groups of records. See the full tutorial here.
Sequentially Numbering Records by Having an Identity Column
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
Sequentially Numbering Records by Using a Temporary Table
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
Sequentially Numbering Records by Altering 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
Sequentially Numbering Records by Using a 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
Sequentially Number Records by Using a Cursor
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
Sequentially Numbering Groups of Records
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