How to Find Least Non-Null Column in One Particular Row in SQL

How to find least non-null column in one particular row in SQL?

Unfortunately (for your case) behaviour of LEAST was changed in MySQL 5.0.13 (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_least) - it used to return NULL only if all arguments are NULL.

This change was even reported as a bug: http://bugs.mysql.com/bug.php?id=15610
But the fix was only to MySQL documentation, explaining new behaviour and compatibility break.

Your solution was one of the recommended workarounds. Another can be using IF operator:

SELECT IF(Col1 IS NULL OR Col2 IS NULL, COALESCE(Col1, Col2), LEAST(Col1,Col2))

SQL query to find columns having at least one non null value

I would not recommend using count(distinct) because it incurs overhead for removing duplicate values. You can just use count().

You can construct the query for counts using a query like this:

select count(col1) as col1_cnt, count(col2) as col2_cnt, . . .
from t;

If you have a list of columns you can do this as dynamic SQL. Something like this:

declare @sql nvarchar(max);

select @sql = concat('select ',
                     string_agg(concat('count(', quotename(s.value), ') as cnt_', s.value),
                      ' from t'
                    )
from string_split(@list) s;

exec sp_executesql(@sql);

This might not quite work if your columns have special characters in them, but it illustrates the idea.

how to select rows with no null values (in any column) in SQL?

You need to explicitly list each column. I would recommend:

select t.*
from t
where col1 is not null and col2 is not null and . . . 

Some people might prefer a more concise (but slower) method such as:

where concat(col1, col2, col3, . . . ) is not null

This is not actually a simple way to express this, although you can construct the query using metadata table or a spreadsheet.

SQL select all rows where at least one column is not NULL

using row_number() to return at least 1 row per first_name, prioritizing those with a non-null column first.

select *
from (
  select t.*
    , row_number() over (
        partition by first_name 
        order by case when job_start is not null
                or title is not null
                or visit_hours is not null
                or name is not null
                or leave_start is not null
                or leave_end is not null
          then 0 
          else 1 
          end
        ) as rn
  from t
) sub
where rn = 1
  or (job_start is not null
    or title is not null
    or visit_hours is not null
    or name is not null
    or leave_start is not null
    or leave_end is not null
  )

Select rows in SQL with most dense (fewest NULL) column data

With this query:

select id, 
  max(
    (case when col1 is not null then 1 else 0 end) +   
    (case when col2 is not null then 1 else 0 end) + 
    (case when col3 is not null then 1 else 0 end)
  ) maxnotnulls
from tablename
group by id

you can get for each id the maximum number of not null columns for each id.

So you can join the table with the above query like this:

select t.* from tablename t
inner join (
  select id, 
    max(
      (case when col1 is not null then 1 else 0 end) +   
      (case when col2 is not null then 1 else 0 end) + 
      (case when col3 is not null then 1 else 0 end)
    ) maxnotnulls
  from tablename
  group by id
) g 
on 
  g.id = t.id
  and 
  (case when t.col1 is not null then 1 else 0 end) +   
  (case when t.col2 is not null then 1 else 0 end) + 
  (case when t.col3 is not null then 1 else 0 end) = g.maxnotnulls

---

Related Topics