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: 
You can use `GROUP BY` for that:

```
SELECT
    ID, MAX(Z) -- Could be MIN(Z)
FROM MyTable
GROUP BY ID

```

Aggregate functions ignore `NULL`s, returning them only when all values on the group are `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                    

```