prash32 prash32 - 1 month ago 7
MySQL Question

Sort and return rows with maximum not null value columns - MYSQL

I have searched Google, Stackoverflow and numerous other website for 2 days now. I am not able to come up with the logic for it.

I have 5 columns.


col1 |col2 |col3 |col4 | col5
2000 | null | 1000 | null | null
5000 | 1000 | null | null | null
null | null | null | null | null
1000 | 100 | 250 | 600 | 111
4000 | 400 | 350 | null | 111


Sorry for the messed up table above. Still new at stackoverflow.

Now, I want to write a select query which will do the following:

1) Arrange these 5 rows in such a way that the rows with the maximum not null values will appear first. And, gradually it ends with a row of maximum null values.

2) Not return a row where all the columns are null.

For the above example, we should get row4 first then row5 then row1 then row2. Note that row3 is not returned as all the values are null.

Till now I have tried the query below which has come close to solving it but its not exact enough.

SELECT *
FROM table
WHERE col1 IS NOT NULL
OR col2 IS NOT NULL
OR col3 IS NOT NULL
OR col4 IS NOT NULL
OR col5 IS NOT NULL
ORDER BY CASE
WHEN col1 IS NULL THEN 1
ELSE 0
END,
col1 DESC,
CASE
WHEN col2 IS NULL THEN 1
ELSE 0
END,
col2 DESC,
CASE
WHEN col3 IS NULL THEN 1
ELSE 0
END,
col3 DESC,
CASE
WHEN col4 IS NULL THEN 1
ELSE 0
END,
col4 DESC,
CASE
WHEN col5 IS NULL THEN 1
ELSE 0
END,
col5 DESC

Answer

The simplest way is to count the number of null values:

select t.*
from t
where (col1 is not null) or (col2 is not null) or (col3 is not null) or
      (col4 is not null) or (col5 is not null)
order by (col1 is not null) + (col2 is not null) + (col3 is not null) +
         (col4 is not null) + (col5 is not null);

This uses the MySQL short-cut that treats a boolean value as an integer.