DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to sequence the output for clothing products?

Execute the following SQL Server T-SQL scripts in SSMS Query Editor to add a sequence column to the clothing line product search:

-- T-SQL sequence numbering prior to SQL Server 2005

USE AdventureWorks;

SELECT   SeqNo = IDENTITY(INT,100,1),

         p.Name,

         p.ProductNumber,

         p.Color,

         p.StandardCost,

         p.ListPrice

INTO     #ClothingList

FROM     Production.Product  p

         INNER JOIN Production.ProductSubCategory psc

           ON p.ProductSubCategoryID = psc.ProductSubCategoryID

         INNER JOIN Production.ProductCategory pc

           ON psc.ProductCategoryID = pc.ProductCategoryID

WHERE    pc.Name = 'Clothing'

ORDER BY ListPrice DESC

 

SELECT *  FROM   #ClothingList

GO

/* Partial results

SeqNo Name                          ProductNumber     Color

100   Men's Bib-Shorts, S           SB-M891-S         Multi

101   Men's Bib-Shorts, M           SB-M891-M         Multi

102   Men's Bib-Shorts, L           SB-M891-L         Multi

103   Women's Tights, S             TG-W091-S         Black

104   Women's Tights, M             TG-W091-M         Black

105   Women's Tights, L             TG-W091-L         Black

106   Women's Mountain Shorts, S    SH-W890-S         Black

*/

 

-- T-SQL sequence numbering SQL Server 2005 and on

SELECT   SeqNo = 100 + ROW_NUMBER()

                         OVER(ORDER BY ListPrice DESC),

         p.Name,

         p.ProductNumber,

         p.Color,

         p.StandardCost,

         p.ListPrice

FROM     Production.Product  p

         INNER JOIN Production.ProductSubCategory psc

           ON p.ProductSubCategoryID = psc.ProductSubCategoryID

         INNER JOIN Production.ProductCategory pc

           ON psc.ProductCategoryID = pc.ProductCategoryID

WHERE    pc.Name = 'Clothing'

ORDER BY SeqNo

GO

/* Partial results

SeqNo Name                          ProductNumber     Color

101   Men's Bib-Shorts, S           SB-M891-S         Multi

102   Men's Bib-Shorts, M           SB-M891-M         Multi

103   Men's Bib-Shorts, L           SB-M891-L         Multi

104   Women's Tights, S             TG-W091-S         Black

105   Women's Tights, M             TG-W091-M         Black

106   Women's Tights, L             TG-W091-L         Black

107   Women's Mountain Shorts, S    SH-W890-S         Black

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE