NTILE and NULLS LAST/FIRST

The null values are calculated as a value for the NTILE and put to the beginning if NULLS FIRST or to the ending if NULLS LAST.


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', 1600,    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',    NULL,    20);

SQL> select empno,
  2         ename,
  3         sal,
  4         NTILE(2) over (order by sal NULLS LAST) n2,
  5         NTILE(3) over (order by sal NULLS LAST) n3,
  6         NTILE(4) over (order by sal NULLS LAST) n4
  7  from emp;

     EMPNO ENAME             SAL         N2         N3         N4
---------- ---------- ---------- ---------- ---------- ----------
         3 WARD             1250          1          1          1
         5 MARTIN           1250          1          1          1
        10 TURNER           1500          1          1          1
         2 ALLEN            1600          1          1          2
         6 BLAKE            2850          1          2          2
         7 CLARK            2850          1          2          2
         4 JONES            2975          2          2          3
         8 SCOTT            3000          2          2          3
         9 KING             3000          2          3          3
         1 SMITH                          2          3          4
        11 ADAMS                          2          3          4

11 rows selected.

SQL>
SQL> select empno,
  2         ename,
  3         sal,
  4         NTILE(2) over (order by sal NULLS FIRST) n2,
  5         NTILE(3) over (order by sal NULLS FIRST) n3,
  6         NTILE(4) over (order by sal NULLS FIRST) n4
  7  from emp;

     EMPNO ENAME             SAL         N2         N3         N4
---------- ---------- ---------- ---------- ---------- ----------
         1 SMITH                          1          1          1
        11 ADAMS                          1          1          1
         3 WARD             1250          1          1          1
         5 MARTIN           1250          1          1          2
        10 TURNER           1500          1          2          2
         2 ALLEN            1600          1          2          2
         7 CLARK            2850          2          2          3
         6 BLAKE            2850          2          2          3
         4 JONES            2975          2          3          3
         9 KING             3000          2          3          4
         8 SCOTT            3000          2          3          4

11 rows selected.

SQL>
Home »
Oracle »
Analytical Functions » 

NTILE:
  1. NTILE
  2. NTILE data into different groups
  3. NTILE and Null values
  4. NTILE and NULLS LAST/FIRST
Related: