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

This is a prioritization query. One method is to use row_number(). If you want only one value per Com_no/Company/Loc, then:

select x.*
from (select x.*,
             row_number() over (partition by Com_no, Company, Loc order by Rewards nulls last) as seqnum
      from x
     ) x
where seqnum = 1;

Or even:

select Com_no, Company, Loc, max(Rewards)
from x
group by Com_no, Company, Loc;