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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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