CODEWITHSUNDEEP
sqlsql-server
hunB
hunB

Reputation: 331

Select rows where a column is not null if multiple values or null if 1 value

I have a table containing titles and values.

For a distinct title, I want to retrieve all not null values, except if this title only has a NULL value.

A sample of my table looks like this:

Title    Value          
---------------
ex1      8
ex1      9
ex1      NULL
ex2      8
ex2      NULL
ex3      NULL

In this example I want my desired output would look like this:

Libelle  TPO_code   
--------------------        
ex1      8
ex1      9
ex2      8
ex3      NULL

I can achieve retrieving all but NULL values with the following request but I am blocked for the case a title has only a NULL value :

select distinct Title, Value 
from mytable
where Value is not null

Upvotes: 2

Views: 3850

Answers (7)

Matthew Baker
Matthew Baker

Reputation: 2729

Other options:

DECLARE @myTable AS TABLE (Title CHAR(4) NOT NULL, Value INT NULL);

INSERT INTO @myTable (Title, Value)
VALUES ('ex1', 8)
,      ('ex1', 9)
,      ('ex1', NULL)
,      ('ex2', 8)
,      ('ex2', NULL)
,      ('ex3', NULL);

-- Original
SELECT      DISTINCT
            T1.Title
,           T2.Value
  FROM      @myTable T1
  LEFT JOIN @myTable T2 ON  T2.Title = T1.Title
                       AND  T2.Value IS NOT NULL;

-- Common Table Expression example
WITH cte AS
    (SELECT *
     ,      ROW_NUMBER() OVER (PARTITION BY Title
ORDER BY Value DESC) RN
       FROM @myTable)
SELECT  cte.Title
,       cte.Value
  FROM  cte
 WHERE  RN = 1
   OR   cte.Value IS NOT NULL

If you run through a common table expression and assign row numbers, you can ensure at least one row per "Title" without the multiple table hits. If you run the execution plans on the two side by side I'm seeing a saving on the cte version over the join. Will depend on how much data is going in though as the sort may become more expensive than the join - although the join version requires a sort for the distinct.

Give them a try on real data and get some timings. let me know if this helps.

Upvotes: 1

shan kumaran
shan kumaran

Reputation: 41

SELECT      
    DISTINCT a.Title,           
             b.Value   
FROM      
    mytable a   
LEFT JOIN 
    mytable b 
ON  
    a.Title = b.Title 
AND  
    b.Value IS NOT NULL;

Upvotes: 0

Mahesh
Mahesh

Reputation: 1

I can suggest below solution for your problem,

select title,value from mytable where id is not null
union
select title,value from (
  select title,value, dense_rank() over (partition by title order by value)  rank from mytable)
where rank=1

Upvotes: 0

Matthew Baker
Matthew Baker

Reputation: 2729

You can avoid using the unions if you want and try this:

DECLARE @myTable AS TABLE (Title CHAR(4) NOT NULL, Value INT NULL);

INSERT INTO @myTable (Title, Value)
VALUES ('ex1', 8)
,      ('ex1', 9)
,      ('ex1', NULL)
,      ('ex2', 8)
,      ('ex2', NULL)
,      ('ex3', NULL);

SELECT      DISTINCT
            T1.Title
,           T2.Value
  FROM      @myTable T1
  LEFT JOIN @myTable T2 ON  T2.Title = T1.Title
                       AND  T2.Value IS NOT NULL;

I'd suggest trying all of these options against the shape of your real data to find the most efficient version. Is also worth spending some time checking indexes etc to make these quicker.

Upvotes: 2

GuidoG
GuidoG

Reputation: 12014

Another option is to use a union and a subquery

declare @t table (Title varchar(10), Value int)
insert into @t (Title, Value)
values ('ex1', 8), ('ex1', 9), ('ex1', null), ('ex2', 8), ('ex2', null), ('ex3', null)

-- first get all rows with Value different from null
select t.Title,
       t.Value
from   @t t
where  t.Value is not null

union 

-- now also get all rows with just one row and where that row has null in value
select t.Title,
       t.Value
from   @t t
where  t.Title in ( select t2.title from @t t2 group by t2.Title having count(t2.Title) = 1 )
and    t.Value is null

this will return this result

Title   Value   
-----   -----   
ex1     8   
ex1     9   
ex2     8   
ex3     null

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Try this

;WITH CTE (Title, Value)
AS
(          
SELECT 'ex1', 8    UNION ALL
SELECT 'ex1', 9    UNION ALL
SELECT 'ex1', NULL UNION ALL
SELECT 'ex2', 8    UNION ALL
SELECT 'ex2', NULL UNION ALL
SELECT 'ex3', NULL

)
,CTe2
AS
(
SELECT Title, Value, COUNT(Title)OVER(PARTITION BY Title ORDER BY Title) cnt
FROM CTE
)
SELECT  Title, 
        Value 
FROM CTe2 WHERE ISNULL(Value,1) <> 1
UNION ALL
SELECT  Title,
        Value 
FROM CTe2 WHERE cnt =1

Result

Title   Value
-------------
ex1      8
ex1      9
ex2      8
ex3      NULL

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15130

You can use a NOT EXISTS:

SELECT  DISTINCT T.Title
,       T.Value 
FROM    mytable T
WHERE   T.Value IS NOT NULL
    OR  NOT EXISTS (
                        SELECT  NULL
                        FROM    mytable T2
                        WHERE   T2.Value IS NOT NULL
                            AND T2.Title = T1.Title
                    )

Upvotes: 5

Related Questions