Ignore the Null value in analytical functions

We can use where clause to exclude the null values:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',    NULL,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', NULL,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);


SQL> select ename, sal, DENSE_RANK() over (order by sal NULLS FIRST) toprank
  2  from emp
  3  where sal is not null
  4  order by sal;

ENAME             SAL    TOPRANK
---------- ---------- ----------
MARTIN           1250          1
WARD             1250          1
ADAMS            1500          2
TURNER           1500          2
BLAKE            2850          3
CLARK            2850          3
JONES            2975          4
KING             3000          5
SCOTT            3000          5

9 rows selected.

SQL>
SQL> select ename, sal, DENSE_RANK() over (order by sal NULLS LAST) toprank
  2  from emp
  3  where sal is not null
  4  order by sal;

ENAME             SAL    TOPRANK
---------- ---------- ----------
MARTIN           1250          1
WARD             1250          1
ADAMS            1500          2
TURNER           1500          2
BLAKE            2850          3
CLARK            2850          3
JONES            2975          4
KING             3000          5
SCOTT            3000          5

9 rows selected.

SQL>
Home »
Oracle »
Analytical Functions » 

Null_Values:
  1. Null value and analytical functions
  2. Ignore the Null value in analytical functions
  3. Handle Null values with NVL function and use that value in analytical functions
  4. NVL and NULLS Last option together
Related: