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

Question

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

Answer 1

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
                    )

Answer 2

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.