|
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
*/ |
