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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By Gregory A. Larsen
developerWorks - FREE Tools! |
Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance. FREE! Go There Now!
|
|
|
|
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve. FREE! Go There Now!
|
|
|
|
Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success. FREE! Go There Now!
|
|
|
|
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer. FREE! Go There Now!
|
|
|
|
Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise. FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications. FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of WebSphere Extended Deployment Compute Grid, which lets you schedule, execute, and monitor batch jobs. Because online transaction processing and batch jobs execute simultaneously on the same server resources, you can avoid costly duplication of resources. Compute Grid supports job types of Java transactional batch, compute-intensive and a new type called "native execution", which enables non-Java workloads to run on distributed end points. FREE! Go There Now!
|
|
|
|
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available. FREE! Go There Now!
|
|
|
|
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time. FREE! Go There Now!
|
|
|
|
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential. FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools! |