title: SQL OVER (Partiton by) - Handle nulls
body: <p>I have a following scenario:</p>
  
  <p>Table Employees:</p>
  
  <pre><code>First Name | Last Name | Department | Salary
  -----------|-----------|------------|---------
  John       | Doe       | Finance    | 20
  John       | Doe       | R&amp;D        | 20
  John       | null      | Finance    | 20
  John       | long      | Finance    | 20
  </code></pre>
  
  <p>and I want 1 row for each (First Name,Last Name),
  unless we have a null in the last name, and then i want just 1 row with (First Name,null)</p>
  
  <p>for the above example the result is:</p>
  
  <pre><code>First Name | Last Name | Department | Salary
  -----------|-----------|------------|---------
  John       | null      | Finance    | 20
  </code></pre>
  
  <p>but if i didn't have that record then the result should have been:</p>
  
  <pre><code>First Name | Last Name | Department | Salary
  -----------|-----------|------------|---------
  John       | Doe       | R&amp;D        | 20
  John       | long      | Finance    | 20
  </code></pre>
  
  <p>I guess the answer involves some Partition By-s, but I'm not sure where.</p>
  
  <p>Right now I came to this:</p>
  
  <pre><code>SELECT FirstName,LastName, DEPARTMENT,Salary,RK FROM 
  (
  select * from 
      SELECT EXT.*, 
      ROW_NUMBER() OVER(PARTITION BY EXT.FirstName,EXT.LastName 
      ORDER BY rownum ASC) AS RK
        FROM Employees EXT   
  )
  WHERE RK = 1 ;
  </code></pre>
  
  <p>Thanks !</p>