Eliminating duplicate rows with null values using with clause

Question

How do we eliminate duplicates by only selecting those with values in a certain field using with clause statement?

Query is something like this:

with x as (--queries with multiple join tables, etc.)
select distinct * from x

Output below:

Com_no   Company      Loc    Rewards
1         Mccin      India      50
1         Mccin      India
2         Rowle      China      18
3         Draxel     China      11
3         Draxel     China  
4         Robo       UK          

As you can see, I get duplicate records. I want to get rid of the null values that are NOT unique. That is to say, Robo is unique since it only has 1 record with a null value in Rewards, so I want to keep that.

I tried this:

 with x as (--queries with multiple join tables, etc.)
 select distinct * from x where Rewards is not null

And of course that wasn't right, since it also got rid of 4 Robo UK

Expected output should be:

1         Mccin      India      50
2         Rowle      China      18
3         Draxel     China      11 
4         Robo       UK      

Answer 1

The problem is you're calling those rows duplicates, but they're not duplicates. They're different. So what you want to do is exclude rows where Rewards is null UNLESS there aren't any rows with a not null value, and then select the distinct rows. So something like:

select distinct * 
from x a
where Rewards is not null 
or (Rewards is null and not exists (select 1 from x b where a.Com_no = b.Com_no 
    and b.Rewards is not null)

Now your Robo row will still be included as there isn't a row in x for Robo where Rewards is not null, but the rows for the other Companies with null Rewards will be excluded as there are not null rows for them.