|
Execute the following
Microsoft SQL Server T-SQL sample code in SSMS Query Editor or Query Analyzer to sequence number query output. Starting with SQL Server 2005, the ROW_NUMBER() function can be used for sequential numbeing without a special loop.
-- Number output with ROW_NUMBER() ranking function
SELECT SeqNo = ROW_NUMBER () OVER (ORDER BY au_lname, au_fname ),
Author = left(rtrim(au_lname),28) + ', ' + left(rtrim(au_fname),20)
FROM pubs.dbo.authors
WHERE au_lname < 'Smith'
ORDER BY SeqNo
------------
-- Number output in WHILE loop
USE pubs
DECLARE @FullName VARCHAR(50),
@n INT
DECLARE CursorAuthor CURSOR FOR
SELECT left(rtrim(au_lname),28) + ', ' + left(rtrim(au_fname),20)
FROM pubs.dbo.authors
WHERE au_lname < 'Smith'
ORDER BY au_lname,
au_fname
OPEN CursorAuthor
FETCH NEXT FROM CursorAuthor
INTO @FullName
-- Header Print
PRINT 'SeqNo' + cast('' AS CHAR(7)) + 'Author'
PRINT '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
SELECT @n = 0
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @n = @n + 1
PRINT cast(@n AS CHAR(7)) + rtrim(@FullName) -- TAB right
FETCH NEXT FROM CursorAuthor
INTO @FullName
END
CLOSE CursorAuthor
DEALLOCATE CursorAuthor
GO
/*
SeqNo Author
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 Bennet, Abraham
2 Blotchet-Halls, Reginald
3 Carson, Cheryl
4 DeFrance, Michel
5 del Castillo, Innes
6 Dull, Ann
7 Green, Marjorie
8 Greene, Morningstar
9 Gringlesby, Burt
10 Hunter, Sheryl
11 Karsen, Livia
12 Locksley, Charlene
13 MacFeather, Stearns
14 McBadden, Heather
15 O'Leary, Michael
16 Panteley, Sylvia
17 Ringer, Albert
18 Ringer, Anne
*/
|
