How to order by multiple columns with same datatype [closed]

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.

Output should be:

I have 3 columns in which each row will have only of value of the three columns. I need to sort all the rows irrespective of the column (basically consider all the values appear in a single column, and sort). In the example above, dave is lowest according to sort order, so that row should come first. following by jeff row, followed by Maccafrey, Matt, Tom, TomBrady, Zedaya.

The right solution has been ticked as correct. Used Contact to order by all the fields at the same time.

Solution 1:

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