Easiest way to eliminate NULLs in SELECT DISTINCT?

sql, sql-server

Solution

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

Problem

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.

Original source