Easiest way to eliminate NULLs in SELECT DISTINCT?

Question

I am working on a query that is fairly similar the following:

CREATE TABLE #test (a char(1), b char(1))  INSERT INTO #test(a,b) VALUES  ('A',NULL), ('A','B'), ('B',NULL), ('B',NULL)  SELECT DISTINCT a,b FROM #test  DROP TABLE #test 

The result is, unsurprisingly,

a   b ------- A   NULL A   B B   NULL 

The output I would like to see in actuality is:

a   b ------- A   B B   NULL 

That is, if a column has a value in some records but not in others, I want to throw out the row with NULL for that column. However, if a column has a NULL value for all records, I want to preserve that NULL.

What's the simplest/most elegant way to do this in a single query?

I have a feeling that this would be simple if I weren't exhausted on a Friday afternoon.

Answer 1

Try this:

select distinct * from test where b is not null or a in (   select a from test   group by a   having max(b) is null) 

You can get the fiddle here.

Note if you can only have one non-null value in b, this can be simplified to:

select a, max(b) from test group by a 

Answer 2

;WITH CTE     AS     (     SELECT DISTINCT * FROM #test     )     SELECT a,b     FROM CTE             ORDER BY CASE WHEN b IS NULL THEN 9999 ELSE b END ;