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