title: Eliminating duplicate rows with null values using with clause
body: <p>How do we eliminate duplicates by only selecting those with values in a certain field using with clause statement? </p>
  
  <p>Query is something like this:</p>
  
  <pre><code>with x as (--queries with multiple join tables, etc.)
  select distinct * from x
  </code></pre>
  
  <p>Output below:</p>
  
  <pre><code>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          
  </code></pre>
  
  <p>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.</p>
  
  <p>I tried this:</p>
  
  <pre><code> with x as (--queries with multiple join tables, etc.)
   select distinct * from x where Rewards is not null
  </code></pre>
  
  <p>And of course that wasn't right, since it also got rid of <code>4 Robo UK</code></p>
  
  <p>Expected output should be:</p>
  
  <pre><code>1         Mccin      India      50
  2         Rowle      China      18
  3         Draxel     China      11 
  4         Robo       UK      
  </code></pre>