|
Execute the following
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.
USE AdventureWorks;
-- 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
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
*/
|
