NTile with NULLS LAST : NTILE « Analytical Functions « Oracle PL / SQL






NTile with NULLS LAST

 

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL>
SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                   orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       NULL,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), NULL,       58000,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      NULL,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), NULL,       53000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       NULL,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /

1 row created.

SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000             E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82                   58000 E
       105 Robert     15-JAN-84       31000             W
       116 Linda      30-JUL-87                   53000 E
       117 David      31-DEC-90       78000             W
       108 Jode       17-SEP-96       21000       29000 E

7 rows selected.

SQL>
SQL>
SQL>
SQL> -- And with NULLS LAST:
SQL>
SQL> SELECT ename, curr_salary sal,
  2    ntile(2) OVER(ORDER BY curr_salary desc NULLS LAST) n2,
  3    ntile(3) OVER(ORDER BY curr_salary desc NULLS LAST) n3,
  4    ntile(4) OVER(ORDER BY curr_salary desc NULLS LAST) n4,
  5    ntile(5) OVER(ORDER BY curr_salary desc NULLS LAST) n5,
  6    ntile(6) OVER(ORDER BY curr_salary desc NULLS LAST) n6,
  7    ntile(8) OVER(ORDER BY curr_salary desc NULLS LAST) n8
  8  FROM employee;

ENAME             SAL         N2         N3         N4         N5         N6         N8
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Celia           58000          1          1          1          1          1          1
Linda           53000          1          1          1          1          1          2
James           32000          1          1          2          2          2          3
Jode            29000          1          2          2          2          3          4
David                          2          2          3          3          4          5
Alison                         2          3          3          4          5          6
Robert                         2          3          4          5          6          7

7 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;

Table dropped.

SQL>
SQL>

           
         
  








Related examples in the same category

1.NTILE(buckets) to calculate n-tiles: tertiles, quartiles, and so on
2.NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile
3.NTILE(4) function is used for that ordering
4.NTILE(4) in reverse order
5.To get a clearer picture of the NTILE function
6.ntile and row_number
7.ntile as quartile