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

*/

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