uKbCepvy

1Easiest way to eliminate NULLs in SELECT DISTINCT?
2CREATE TABLE #test (a char(1), b char(1))
3
4INSERT INTO #test(a,b) VALUES 
5('A',NULL),
6('A','B'),
7('B',NULL),
8('B',NULL)
9
10SELECT DISTINCT a,b FROM #test
11
12DROP TABLE #test
13	
14a   b
15-------
16A   NULL
17A   B
18B   NULL
19	
20a   b
21-------
22A   B
23B   NULL
24	
25select distinct * from test
26where b is not null or a in (
27  select a from test
28  group by a
29  having max(b) is null)
30	
31select a, max(b) from test
32group by a
33	
34create table test(
35x char(1),
36y char(1)
37);
38
39insert into test(x,y) values
40('a',null),
41('a','b'),
42('b', null),
43('b', null)
44	
45with has_all_y_null as
46(
47    select x
48    from test
49    group by x
50    having sum(case when y is null then 1 end) = count(x)
51)
52select distinct x,y from test
53where 
54
55    (
56        -- if a column has a value in some records but not in others,
57        x not in (select x from has_all_y_null) 
58
59        -- I want to throw out the row with NULL
60        and y is not null 
61    )
62    or 
63    -- However, if a column has a NULL value for all records, 
64    -- I want to preserve that NULL
65    (x in (select x from has_all_y_null))
66
67order by x,y
68	
69X    Y
70 A    B
71 B    NULL
72	
73with has_all_y_null as
74(
75    select x
76    from test
77    group by x
78
79    -- having sum(case when y is null then 1 end) = count(x) 
80    -- should have thought of this instead of the code above. Mosty's logic is good:
81    having max(y) is null
82)
83select distinct x,y from test
84where 
85    y is not null
86    or 
87    (x in (select x from has_all_y_null))
88order by x,y
89	
90select distinct * from test
91where b is not null or a in 
92  ( -- has all b null
93  select a from test
94  group by a
95  having max(b) is null)
96	
97SELECT DISTINCT t.a, t.b
98FROM   #test t
99WHERE  b IS NOT NULL
100OR     NOT EXISTS (SELECT 1 FROM #test u WHERE t.a = u.a AND u.b IS NOT NULL)
101ORDER BY t.a, t.b
102	
103SELECT DISTINCT a, b
104FROM   test t
105WHERE  NOT ( b IS  NULL
106          AND EXISTS 
107              ( SELECT * 
108                FROM test ta 
109                WHERE ta.a = t.a 
110                  AND ta.b IS NOT NULL
111               ) 
112             )
113  AND  NOT ( a IS  NULL
114          AND EXISTS 
115              ( SELECT * 
116                FROM test tb 
117                WHERE tb.b = t.b 
118                  AND tb.a IS NOT NULL
119               ) 
120             )
121	
122SELECT DISTINCT a.a, b.b
123FROM #test a
124    LEFT JOIN #test b ON a.a = b.a
125        AND b.b IS NOT NULL
126	
127SELECT a,b FROM #test t where b is not null
128union
129SELECT a,b FROM #test t where b is null
130and not exists(select 1 from #test where a=t.a and b is not null)
131	
132a    b
133---- ----
134A    B
135B    NULL