PostgreSQL query rows with least null value on columns

Question

How can I query rows where the output would be the rows with least null value on the columns?

My data is:

ID         | col1     | col2      | col3      | col4     
-----------+----------+-----------+-----------+-----------
 1         | Null     |Null       | with value| with value
 2         |with value|Null       | with value| with value
 3         |with value|Null       | Null      | Null

where the result would be:

 ID         | col1     | col2      | col3      | col4     
 -----------+----------+-----------+-----------+-----------
  2         |with value|Null       | with value| with value

Because id 2 is the record with fewest null values. Any help will be greatly appreciated. Thanks

what if there are more rows with equally low number of nulls? — Radim Bača, Jan 08 '19 at 08:24

dani herrera · Accepted Answer · 2019-01-08T08:23:32.357

3

You can:

Order rows by number of nulls (ascending)
Limit rows to 1 ( LIMIT 1 )

Your code:

SELECT *
FROM your_table
ORDER BY 
    CASE WHEN col1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN col2 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN col3 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN col4 IS NULL THEN 1 ELSE 0 END 
LIMIT 1

edited Jan 08 '19 at 08:23

answered Jan 08 '19 at 08:11

dani herrera

48,760
8
117
177

This will return the record with the *most* number of nulls, which is the opposite of what the OP asked for. Also, what happens if there's a tie for rows with the fewest null columns? – APC Jan 08 '19 at 08:22
i think it should be the opposite if its null then 0 else 1 because OP wants less null value – Himanshu Jan 08 '19 at 08:24
@APC, thanks! I fixed it. About "the tie", I have no constraints to code it, OP don't talks about this. – dani herrera Jan 08 '19 at 08:25
Welcome buddy :) – Himanshu Jan 08 '19 at 08:26
Use `= ''` instead of `IS NULL` for empty string. – DevonDahon May 04 '20 at 15:32

score 0 · Answer 2 · answered Jan 08 '19 at 12:15

If you want only one row, then you can do:

select t.*
from t
order by ( (col1 is null)::int + (col2 is null)::int +
           (col3 is null)::int + (col4 is null)::int
         ) asc
fetch first 1 row only;

If you want all such rows, I think I would do:

select t.*
from (select t.*,
             dense_rank() over 
                 (order by (col1 is null)::int + (col2 is null)::int +
                           (col3 is null)::int + (col4 is null)::int
                 ) as null_ranking
      from t
     ) t
where null_ranking = 1;

PostgreSQL query rows with least null value on columns

2 Answers2