|
Execute the following
Microsoft SQL Server T-SQL example scripts in Query Editor to sort the results using the CASE function in the ORDER BY clause. The output of the CASE function is a dynamic column name. The NULLIF function can be used for simple special sorting.
USE AdventureWorks2008;
-- Major sort on Color if not null, else on product name
-- Minor sort on ProductNumber - SQL Case in Order By
SELECT ProductID,
ProductNumber,
ProductName = Name,
Color
FROM Production.Product
WHERE Name LIKE ('%Road%')
ORDER BY CASE
WHEN Color IS NULL THEN Name
ELSE Color
END,
ProductNumber DESC
/* Partial results
ProductID ProductNumber ProductName Color
977 BK-R19B-58 Road-750 Black, 58 Black
999 BK-R19B-52 Road-750 Black, 52 Black
998 BK-R19B-48 Road-750 Black, 48 Black
997 BK-R19B-44 Road-750 Black, 44 Black
813 HB-R956 HL Road Handlebars NULL
512 RM-R800 HL Road Rim NULL
519 SA-R522 HL Road Seat Assembly NULL
913 SE-R995 HL Road Seat/Saddle NULL
933 TI-R982 HL Road Tire NULL
811 HB-R504 LL Road Handlebars NULL
510 RM-R436 LL Road Rim NULL
517 SA-R127 LL Road Seat Assembly NULL
911 SE-R581 LL Road Seat/Saddle NULL
931 TI-R092 LL Road Tire NULL
812 HB-R720 ML Road Handlebars NULL
511 RM-R600 ML Road Rim NULL
518 SA-R430 ML Road Seat Assembly NULL
912 SE-R908 ML Road Seat/Saddle NULL
932 TI-R628 ML Road Tire NULL
717 FR-R92R-62 HL Road Frame - Red, 62 Red
706 FR-R92R-58 HL Road Frame - Red, 58 Red
721 FR-R92R-56 HL Road Frame - Red, 56 Red
720 FR-R92R-52 HL Road Frame - Red, 52 Red
*/
------------
-- Order by LastName, MiddleName if exists else FirstName, & FirstName
-- CASE will pick MiddleName or FirstName for the 2nd sorting term
-- If MiddleName is picked, the final FirstName sort will do an order by
-- within the same LastName & MiddleName entries - SQL Server Case in Order By
USE AdventureWorks;
SELECT FirstName,
MiddleName = COALESCE(MiddleName,''),
LastName,
AddressLine1,
AddressLine2 = COALESCE(AddressLine2,''),
City,
sp.Name AS [State],
cr.Name AS Country,
I.CustomerID
FROM Person.Contact AS c
INNER JOIN Sales.Individual AS I
ON c.ContactID = I.ContactID
INNER JOIN Sales.CustomerAddress AS ca
ON ca.CustomerID = I.CustomerID
INNER JOIN Person.[Address] AS a
ON a.AddressID = ca.AddressID
INNER JOIN Person.StateProvince sp
ON sp.StateProvinceID = a.StateProvinceID
INNER JOIN Person.CountryRegion cr
ON cr.CountryRegionCode = sp.CountryRegionCode
ORDER BY LastName,
CASE
WHEN MiddleName != '' THEN MiddleName
ELSE FirstName
END,
FirstName;
GO
/* Partial results
FirstName MiddleName LastName
Chloe A Adams
Eduardo A Adams
Kaitlyn A Adams
Mackenzie A Adams
Sara A Adams
Adam Adams
Amber Adams
Angel Adams
Aaron B Adams
Noah B Adams
*/
------------
------------
-- Using NULLIF for special sorting - NULL(lowest) values are sorted first ascending
------------
SELECT ProductID,ProductName=Name,Color, ListPrice
FROM AdventureWorks2008.Production.Product
WHERE Color is NOT NULL
ORDER BY NULLIF(Color,'Yellow'), ProductName
------------
/*
.....
964 Touring-3000 Yellow, 58 Yellow 742.35
965 Touring-3000 Yellow, 62 Yellow 742.35
322 Chainring Black 0.00
863 Full-Finger Gloves, L Black 37.99
862 Full-Finger Gloves, M Black 37.99 ....*/ ------------
-- Complex sorting - CTE ORDER BY ROW_NUMBER PARTITION BY - SQL Server 2005 and on
;WITH CTE AS
(SELECT TOP 1 WITH TIES PSC.Name AS SubCategory,
P.Name AS ProductName
FROM Production.ProductSubcategory PSC
INNER JOIN Production.Product P -- alternate LEFT JOIN
ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
ORDER BY ROW_NUMBER()
OVER(PARTITION BY P.ProductSubcategoryID
ORDER BY ProductID) / (10+1) )
SELECT * FROM CTE
ORDER BY Subcategory,
ProductName;
------------ |
