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

Answer 1

You can:

1. Order rows by number of nulls (ascending)
2. 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