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

sql select where multiple columns are not null
how to select not null columns in sql server
select only those columns which are not null
sql query to find null columns in a table
sql check if column is null or empty
sql is null or empty in where clause
sql select only columns with values
sql check if multiple columns are null

I have multiple duplicate IDs, which need to be reduced to a single value. Ordinarily I would use an aggregation method to combine column values (as a sum, mean, etc). Here, I am interested in just keeping the row with the greatest number of non-null values across all the columns:

given this table:

id   col1   col2   col3
1    a      ''     ''
1    a      b      ''
2    x      y      ''
1    a      b      c
2    s      ''     ''

How I could select:

id   col1   col2   col3
2    x      y      ''
1    a      b      c

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

How to SELECT Records With No NULL Values in MySQL, IS NOT NULL Comparison Operator. By far the simplest and most straightforward method for ensuring a particular column's result set doesn't contain NULL values � First, let us create a table and populate it with a few NULL Values.-- Create a Table CREATE TABLE Table1 (Col1 INT); -- Insert Data INSERT INTO Table1 VALUES (1), (2); INSERT INTO Table1 VALUES (NULL),(NULL),(NULL); In the above script, we have inserted 3 NULL values. Now run the following command to count all the NULL values from the table.

Assuming the empty string is really NULL, the simplest method in standard SQL is:

select t.*
from (select t.*,
             row_number() over (partition by id
                                order by ((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) desc
                                         )
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Of course, this readily adapts to comparisons to empty strings.

Selecting Table Data, A query, or SQL SELECT statement, selects data from one or more tables or views. When the result of a query is displayed, records can be in any order, unless you This tutorial shows how to select all columns of the EMPLOYEES table. the salary is at least 11000, and whether the commission percentage is not null. SQL Server provides us with four ranking window functions that help us to rank the provided rows set according to specific column values. These functions are: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE(). All these ranking functions perform the ranking task in its own way, returning the same result when there are no duplicate values in the rows.

You could use an suqery for the max sum of not null and join eg limiting to the top 2

select m.* from  
my_table m
INNER JOIN (
  select id
   , if (col1 is null, 0, 1)  + 
     if (col2 is null, 0,  1)  + 
     if (col3 is null, 0, 1)   result 

  from my_table 
  order by  result desc 
  limit 2
) t on t.id = m.id 

SQL Window Functions, This lesson of the SQL tutorial for data analysis covers SQL windowing functions window function is able to access more than just the current row of the query result. SELECT duration_seconds, SUM(duration_seconds) OVER (ORDER BY be a sum of all the duration_seconds values in its respective start_terminal . Assuming the empty string is really NULL, the simplest method in standard SQL is: select t.* from (select t.*, row_number() over (partition by id order by ((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) desc ) ) as seqnum from t ) t where seqnum = 1;

SQL NULL Values - IS NULL and IS NOT NULL, It is not possible to test for NULL values with comparison operators, such as =, < Select all records from the Customers where the PostalCode column is empty. It’s easy to see we need to remove all records with a NULL or a 2 in the column row_num. Let’s do it with the DELETE command. After that, we need to remove the column row_num. Here’s the code: DELETE FROM sales WHERE row_rank IS NULL OR row_rank = 2; ALTER TABLE sales DROP COLUMN row_rank;

SQL Server DENSE_RANK Function By Practical Examples, This tutorial shows you how to use the SQL Server DENSE_RANK() function to assign a Rows in each partition receive the same ranks if they have the same values. SELECT v, DENSE_RANK() OVER ( ORDER BY v ) my_dense_rank, RANK() OVER CHECK Constraint � UNIQUE Constraint � NOT NULL Constraint � So you're getting the right data, but it's spread over too many rows. That is, instead of 4 rows that have AGE (each with 3 NULL columns and 1 column that has data) you want only 1 AGE row, with the non-NULL value taken from each column. Here's one way you can do that in Oracle 10: WITH cntr AS ( SELECT LEVEL AS r_num

Power Query Find Max Value In Column, The query for finding the price of the most expensive item is very similar to the the MIN and MAX value of the selected column as well as up to the first 10 values . This helps when your data contains cells or rows with blank (null) values, and also need unique values in at least one of the matching columns chosen from� We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique row ID for each row irrespective of the duplicate row. In the following query, we use a RANK function with the PARTITION BY clause. The PARTITION BY clause prepares a subset of data for the specified columns and gives rank for that partition.

Comments
  • Which rdbms are you using and does this table contain only these 4 columns?
  • Could any combination of columns be null, or will it only be [null,null,null],[a,null,null], or [a,b,null]. also, do you really mean NULL, your example has values of '' in those columns
  • Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using postgresql, oracle, db2, sql-server, ...
  • This answer is a bit ridiculous. It may work for the OPs particular data, but it would not do the expected thing if there were more than two ids or if the ids were tied for the number of non-NULL columns.