How to order by multiple columns with same datatype-CodePudding

I want to sort multiple columns at the same time meaning I do not want to sort first column then second column which sql server order by generally does. Below is the example of how I want the result should be. Note that all column have same datatype.Also, Note that one of the columns in a row only have data.

I have a table that have values I want to sort this table by all the values in the three columns such that rows should populate as per the ascending or descending order of each value in the row. Below table sholud be populated as

Thanks for your help.

CodePudding user response：

Use a CASE expression in the ORDER BY clause:

ORDER BY
  CASE
    WHEN firstname <> '' THEN 1
    WHEN lastname <> '' THEN 2
    WHEN middlename <> '' THEN 3
  END,
  firstname, lastname, middlename;

CodePudding user response：

Try converting the blanks to ascii values that are last in the table and sorting, does this work for you?

select *
from t
order by 
  IsNull(NullIf(firstname,''),Char(255)),
  IsNull(NullIf(lastname,''),Char(255)),
  IsNull(NullIf(middlename,''),Char(255))

Example Fiddle

CodePudding user response：

You can use numerics to push empty string / null / only spaces to be evaluated last in the first two columns (then you don't need to do it for the third):

SELECT FirstName, LastName, MiddleName
FROM dbo.NameOfTableWhereOnlyOneColumnHasAValue
ORDER BY CASE LEN(RTRIM(FirstName)) WHEN 0 THEN 2 ELSE 1 END, 
         FirstName,
         CASE LEN(RTRIM(LastName))  WHEN 0 THEN 2 ELSE 1 END, 
         LastName,
         MiddleName;

The advantage over <> '' is just that LEN(RTRIM()) also handles SPACE(n) and NULL.

Example db<>fiddle