Oracle SQL - Use NTILE function for several domains

Introduction

To get a clearer picture of the NTILE function, we can use it with several domains like this:

SELECT ename, curr_salary sal, 
   ntile(2) OVER(ORDER BY curr_salary desc) n2, 
   ntile(3) OVER(ORDER BY curr_salary desc) n3, 
   ntile(4) OVER(ORDER BY curr_salary desc) n4, 
   ntile(5) OVER(ORDER BY curr_salary desc) n5, 
   ntile(6) OVER(ORDER BY curr_salary desc) n6, 
   ntile(8) OVER(ORDER BY curr_salary desc) n8 
FROM emp;

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w w w .j  av a2s . com

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL>
SQL> SELECT ename, curr_salary sal,
  2     ntile(2) OVER(ORDER BY curr_salary desc) n2,
  3     ntile(3) OVER(ORDER BY curr_salary desc) n3,
  4     ntile(4) OVER(ORDER BY curr_salary desc) n4,
  5     ntile(5) OVER(ORDER BY curr_salary desc) n5,
  6     ntile(6) OVER(ORDER BY curr_salary desc) n6,
  7     ntile(8) OVER(ORDER BY curr_salary desc) n8
  8  FROM emp;

ENAME             SAL         N2         N3         N4         N5         N6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        N8
----------
Alison                         1          1          1          1          1
         1

Robert                         1          1          1          1          1
         2

David                          1          1          2          2          2
         3


ENAME             SAL         N2         N3         N4         N5         N6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        N8
----------
Celia           58000          1          2          2          2          3
         4

Linda           53000          2          2          3          3          4
         5

James           32000          2          3          3          4          5
         6


ENAME             SAL         N2         N3         N4         N5         N6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        N8
----------
Jode            29000          2          3          4          5          6
         7


7 rows selected.

SQL>

Oracle SQL - Use NTILE function for several domains

Introduction

Demo

Related Topic