Question: This is kind of hard to explain in words but here is an example of what I am trying to do in SQL. I have a query which returns the following records:

```
ID     Z
---   ---
1      A
1      <null>
2      B
2      E
3      D
4      <null>
4      F
5      <null>

```

I need to filter this query so that each unique record (based on ID) appears only once in the output and if there are multiple records for the same ID, the output should contain the record with the value of Z column being **non-null**. If there is only a single record for a given ID and it has value of null for column Z the output still should return that record. So the output from the above query should look like this:

```
ID     Z
---   ---   
1      A
2      B
2      E
3      D
4      F
5      <null> 

```

How would you do this in SQL?

Answer: 
If you need to return both 2-B and 2-E rows:

```
SELECT * 
FROM YourTable t1
WHERE Z IS NOT NULL
    OR NOT EXISTS 
        (SELECT * FROM YourTable  t2 
         WHERE T2.ID = T1.id AND T2.z IS NOT NULL)

```
```
    with tmp (id, cnt_val) as                      
    (select id,                                              
            sum(case when z is not null then 1 else 0 end) 
     from   t                                                
     group  by id)                                           
    select t.id, t.z                                         
    from t                                                   
    inner join tmp on t.id = tmp.id                          
    where tmp.cnt_val > 0 and t.z is not null                
       or tmp.cnt_val = 0 and t.z is null                    

```