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 implement T-SQL complex sorting?

Execute the following Microsoft SQL Server T-SQL example scripts in Query Editor to sort the results using the ROW_NUMBER() and CASE functions in the ORDER BY clause. The NULLIF function can also be used for simple special sorting.

-- List top priced item for each product color

SELECT   TOP 1 WITH TIES Color = coalesce(Color,'N/A'),

                         ListPrice,

                         ProductName = Name,

                         ProductID

FROM     AdventureWorks2008.Production.Product

ORDER BY ROW_NUMBER()

           OVER(PARTITION BY Color ORDER BY ListPrice DESC);

/*    Color       ListPrice  ProductName       ProductID

      N/A         229.49     HL Fork                 804

      Black       3374.99    Mountain-100 Black, 38  775

      Red         3578.27    Road-150 Red, 62        749

      Silver      3399.99    Mountain-100 Silver, 38 771

      Blue        2384.07    Touring-1000 Blue, 46   966

      Grey        125.00     Touring-Panniers, Large 842

      Multi       89.99      Men's Bib-Shorts, S     855

      Silver/Black 80.99      HL Mountain Pedal       937

      White       9.50       Mountain Bike Socks, M  709

      Yellow      2384.07    Touring-1000 Yellow, 46 954   */

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

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;

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

Related link:

Slightly more dynamic ORDER BY in SQL Server 2005

 

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