NVL and NULLS Last option together


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, NVL(sal,1000), DENSE_RANK() over (order by NVL(sal,1000) NULL
S LAST) toprank
  2  from emp
  3  order by sal;

ENAME      NVL(SAL,1000)    TOPRANK
---------- ------------- ----------
MARTIN              1250          2
WARD                1250          2
ADAMS               1500          3
TURNER              1500          3
CLARK               2850          4
BLAKE               2850          4
JONES               2975          5
KING                3000          6
SCOTT               3000          6
SMITH               1000          1
ALLEN               1000          1

11 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: