Sort results by number of NOT NULL values

Salman A Published at Dev

Salman A

I have a query where each row consists of 3 columns:

Name
Distance
Proximity

I want to sort the rows based on number of NOT NULL (i.e. present) values exactly as follows:

All values are present
Two values are present in this order
- Name and Distance
- Name and Proximity
- Distance and Proximity
One value is present
- Name
- Distance
- Proximity

Here is sample data (insert statements are sorted in the order i expect):

/*
CREATE TABLE #TEMP (
    Type      VARCHAR(100),
    Name      VARCHAR(100),
    Distance  VARCHAR(100),
    Proximity VARCHAR(100)
);
*/
INSERT INTO #TEMP VALUES ('AIRPORT', 'KBLI', '21mi', 'City')
INSERT INTO #TEMP VALUES ('AIRPORT', 'KBLI', '21mi',  NULL )
INSERT INTO #TEMP VALUES ('AIRPORT', 'KBLI',  NULL , 'City')
INSERT INTO #TEMP VALUES ('AIRPORT',  NULL , '21mi', 'City')
INSERT INTO #TEMP VALUES ('AIRPORT', 'KBLI',  NULL ,  NULL )
INSERT INTO #TEMP VALUES ('AIRPORT',  NULL , '21mi',  NULL )
INSERT INTO #TEMP VALUES ('AIRPORT',  NULL ,  NULL , 'City')

I have had some success with COALESCE statement but I am looking for something efficient and readable. Later I will change to four columns.

Bohemian

Assign a present value as if it were a number (a name=4, a distance=3, a proximity=2), then sum them and sort by that:

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

The trick here is that 3+2 > 4, so a distance and proximity beats a name.

Collected from the Internet

Please contact [email protected] to delete if infringement.