SQLUSA
SQL Server 2005 Administration
Multi-Dimensional Business Intelligence
SQL Server 2005 Programming
Crash Course in SQL Server 2005

Microsoft SQL Server 2005 Best Practices

How to apply CASE in ORDER BY for complex sorting?

 

Execute the following script 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;

 

-- Order by (descending) SellEndDate if not null, else SellStartDate

select * from Production.Product

where Name like ('%Road%')

order by

      case

      when SellEndDate IS NULL

      then SellStartDate

      else SellEndDate

      end

desc

 

-- 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

 

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

    JOIN Sales.Individual AS I

            ON c.ContactID = I.ContactID

    JOIN Sales.CustomerAddress AS ca

            ON ca.CustomerID = I.CustomerID

    JOIN Person.[Address] AS a

            ON a.AddressID = ca.AddressID

    JOIN Person.StateProvince sp

            ON  sp.StateProvinceID = a.StateProvinceID

    JOIN Person.CountryRegion  cr

            ON cr.CountryRegionCode = sp.CountryRegionCode

ORDER BY LastName,

         CASE

         WHEN MiddleName != '' THEN MiddleName

         ELSE FirstName END,

         FirstName ;

GO

 

SQLUSA - The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page