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