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

Question

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

I think this answer here might help you.

Vijay Chavda
– Vijay Chavda

2016-10-11 12:07:03 +00:00
Commented Oct 11, 2016 at 12:07 — Vijay Chavda
– Vijay Chavda, Commented Oct 11, 2016 at 12:07

Gordon Linoff · Accepted Answer · 2016-10-11 12:08:25Z

2

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.

answered Oct 11, 2016 at 12:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christian · Accepted Answer · 2016-10-11 12:14:42Z

1

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

edited Oct 11, 2016 at 12:14

Christian

8276 silver badges15 bronze badges

answered Oct 11, 2016 at 12:10

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

3452 silver badges21 bronze badges

1 Comment

prash32 Over a year ago

All the above suggested solutions didn't work. They all return the correct data but its not ordered in such a way that the rows with maximum not null values come at the top. how do we sort it in such a manner.

Giorgos Betsos · Accepted Answer · 2016-10-12 05:04:33Z

1

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

edited Oct 12, 2016 at 5:04

answered Oct 11, 2016 at 12:05

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

8 Comments

prash32 Over a year ago

Still the sorting doesn't work. Should I do the sorting in PHP code or there can be a solution for this?

Giorgos Betsos Over a year ago

@prash32 It should work. Can you provide some sample data demonstrating the problem?

prash32 Over a year ago

for the same columns as in the question your query returns with all the rows as it is and count_nulls = 0

Giorgos Betsos Over a year ago

@prash32 The issue that you mention is not reproducible. Please check the attached fiddle demo.

prash32 Over a year ago

Hi, Yes works on my database as well but I think MySQL treats null values as empty string i.e. no value inside "" quotation marks. I have replaced col1 IS NULL with col1 == "" and it worked. Not sure why IS NULL doesn't work.

|

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related