sql - Sort results by number of NOT NULL values -

i have query each row consists of 3 columns:

1. name
2. distance
3. proximity

i want sort rows based on number of not null (i.e. present) values follows:

1. all values present
2. two values present in order
   • name , distance
   • name , proximity
   • distance , proximity
3. one value present
   • name
   • distance
   • proximity

here sample data (insert statements sorted in order expect):

/* create table #temp (     type      varchar(100),     name      varchar(100),     distance  varchar(100),     proximity varchar(100) ); */ insert #temp values ('airport', 'kbli', '21mi', 'city') insert #temp values ('airport', 'kbli', '21mi',  null ) insert #temp values ('airport', 'kbli',  null , 'city') insert #temp values ('airport',  null , '21mi', 'city') insert #temp values ('airport', 'kbli',  null ,  null ) insert #temp values ('airport',  null , '21mi',  null ) insert #temp values ('airport',  null ,  null , 'city') 

i have had success coalesce statement looking efficient , readable. later change 4 columns.

assign present value if number (a name=4, distance=3, proximity=2), sum them , sort that:

select ... ... order   case when name null 0 else 4 end +   case when distance null 0 else 3 end +   case when proximity null 0 else 2 end desc 

the trick here 3+2 > 4, distance , proximity beats name.