NTILE and 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', 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) n2,
  5         NTILE(3) over (order by sal) n3,
  6         NTILE(4) over (order by sal) 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>
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: