CODEWITHSUNDEEP
mysqldatabase
prash32
prash32

Reputation: 75

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

Upvotes: 1

Views: 512

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can count the number of NULL valued fields using IF:

SELECT *
FROM (
   SELECT *,
          IF(col1 IS NULL, 1, 0) +  IF(col2 IS NULL, 1, 0) +
          IF(col3 IS NULL, 1, 0) +  IF(col4 IS NULL, 1, 0) +
          IF(col5 IS NULL, 1, 0) AS count_nulls
   FROM table) AS t
WHERE t.count_nulls < 5 
ORDER BY t.count_nulls

Demo here

Upvotes: 1

Владимир Гичев
Владимир Гичев

Reputation: 345

SELECT col1, 
       col2, 
       col3, 
       col4, 
       col5, 
       Sum(col1 + col2 + col3 + col4 + col5) AS total 
WHERE  col1 IS NOT NULL 
       AND col2 IS NOT NULL 
       AND col3 IS NOT NULL 
       AND col4 IS NOT NULL 
       AND col5 IS NOT NULL 
ORDER  BY total

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

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.

Upvotes: 2

Related Questions