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 apply CASE in ORDER BY for complex sorting?

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;

------------

 

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