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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use CASE in ORDER BY?

The following SQL Server T-SQL example scripts sort by using the conditional CASE function in the ORDER BY clause:

-- SQL dynamic order by with case - sql multiple order - sql server dynamic order by

-- MSSQL order by country using specific sort sequence

USE AdventureWorks;

GO

 

SELECT   AddressLine1,

         City,

         [State] = sp.StateProvinceCode,

         PostalCode,

         Country = cr.Name

FROM     Person.[Address] a

         INNER JOIN Person.StateProvince sp

           ON a.StateProvinceID = sp.StateProvinceID

         INNER JOIN Person.CountryRegion cr

           ON sp.CountryRegionCode = cr.CountryRegionCode

ORDER BY (CASE

            WHEN cr.Name = 'United States' THEN 0

            WHEN cr.Name = 'Canada' THEN 1

            WHEN cr.Name = 'United Kingdom' THEN 2

            ELSE 3

          END),

         Country,

         City

 

GO

/* Partial results

 

AddressLine1 City State PostalCode Country
2419 Martindale Dr. Yakima WA  98901 United States
2556 San Remo Ct Yakima WA  98901 United States
855 East Main Avenue Zeeland MI  49464 United States
32605 West 252 Mile Road, Suite 250 Aurora ON  L4G 7N6 Canada
9979 Bayview Drive Barrie ON  L4N Canada
251 Indell Lane Brampton ON  L6W 2T7 Canada

*/

-- MSSQL dynamic sort - dynamic order by - order by case

Use AdventureWorks;

Go

 

Select

      AddressLine1,

      AddressLine2=isnull(AddressLine2,''),

      City,

      [State] = sp.StateProvinceCode,

      PostalCode,

      Country = cr.Name

From Person.[Address] a

Join Person.StateProvince sp

    On a.StateProvinceID = sp.StateProvinceID

Join Person.CountryRegion cr

    On sp.CountryRegionCode = cr.CountryRegionCode

Order by

      (Case

         When Ascii([AddressLine1]) between 65 and 90 then 0 -- Upper case alpha

         When Ascii([AddressLine1]) between 48 and 57 then 1 -- Digits

       Else 2

      End), AddressLine1, City

Go

 

Partial results:

AddressLine1 AddressLine2 City State PostalCode Country
Adirondack Factory Outlet Lake George NY  12845 United States
Alderstr 1849 Braunschweig NW  38001 Germany
Alderstr 2577 Poing SL  66041 Germany
Alderstr 2646 Saarlouis SL  66740 Germany
Alderstr 27 Offenbach SL  63009 Germany

 

 

 

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