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