SQL OVER (Partiton by) - Handle nulls

Question

I have a following scenario:

Table Employees:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | Doe       | Finance    | 20
John       | Doe       | R&D        | 20
John       | null      | Finance    | 20
John       | long      | Finance    | 20

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)

for the above example the result is:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | null      | Finance    | 20

but if i didn't have that record then the result should have been:

First Name | Last Name | Department | Salary
-----------|-----------|------------|---------
John       | Doe       | R&D        | 20
John       | long      | Finance    | 20

I guess the answer involves some Partition By-s, but I'm not sure where.

Right now I came to this:

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 ;

Thanks !

Answer 1

Your problem is in the PARTITION clause. You want every first name where there is a surname unless at least one surname with that first name is NULL, in which case you want only those first names that have a NULL surname.

The answer here is to use RANK() instead of ROW_NUMBER(). RANK() does not create a consecutive list; instead rows with equal values get the same rank.

select firstname, lastname, department, salary, rk 
  from ( select a.*
              , rank() over ( partition by firstname
                                  order by case when lastname is null then 0 
                                                else 1 
                                           end
                                           ) as rnk
           from employees a
                )
where rnk = 1

This works by making the existence of a surname relevant rather than the surname itself.

Two more points:

1. You had a nested select without parenthesis. This won't work.
2. There's no point ordering by ROWNUM. By definition rownum returns rows in the order returned by the statement, which means the rows will always be in the order of the ROWNUM.

Answer 2

something like this:

SQL> create table person
  2  (
  3    fname varchar2(10),
  4    lname varchar2(10),
  5     dept  varchar2(10),
  6     sal   number
  7  );

Table created.

SQL> insert into person values ('John', 'Doe', 'Finance', 20);

1 row created.

SQL> insert into person values ('John', 'Doe', 'R&D', 20);

1 row created.

SQL> insert into person values ('John', '', 'Finance', 20);

1 row created.

SQL> insert into person values ('John', 'Long', 'Finance', 20);

1 row created.

SQL> insert into person values ('Paul', 'Doe', 'R&D', 30);

1 row created.

SQL> insert into person values ('Paul', 'Doe', 'Finance', 30);

1 row created.

SQL> insert into person values ('Paul', 'Long', 'Finance', 30);

1 row created.

SQL> select fname, lname, dept, sal
  2    from (select fname, lname, dept, sal,has_null,
  3                  row_number() over(partition by fname,
  4                                                  case when has_null = 'N' then lname else null end
  5                                    order by lname desc nulls first) rn
  6             from (select fname, lname,
  7                           nvl(max(case  when lname is null then 'Y'
  8                               end) over(partition by fname), 'N') has_null, dept, sal
  9                      from person))
 10   where rn = 1;

FNAME      LNAME      DEPT              SAL
---------- ---------- ---------- ----------
John                  Finance            20
Paul       Doe        R&D                30
Paul       Long       Finance            30