NTILE groups data by sort order into a variable number of percentile groupings.
NTILE function roughly works by dividing the number of rows retrieved into the chosen number of segments.
Then, the percentile is displayed as the segment that the rows fall into.
To know which salaries where in the top 25%, the next 25%, the next 25%, and the bottom 25%, then the NTILE(4) function is used for that ordering (100%/4 = 25%).
The algorithm for the function distributes the values "evenly."
The analytical function NTILE(4) for current salary in emp would be:
SELECT empno, ename, curr_salary, NTILE(4) OVER(ORDER BY curr_salary desc) nt FROM emp;
The range of salaries is broken up into (max - min)/4 for NTILE(4) and the rows are assigned after ranking.
SQL> SQL> drop table emp; Table dropped.-- w w w . j a va 2 s. co m 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 empno, ename, curr_salary, 2 NTILE(4) OVER(ORDER BY curr_salary desc) nt 3 FROM emp; EMPNO ENAME CURR_SALARY NT ---------- ---------- ----------- ---------- 1 Alison 1 4 Robert 1 6 David 2 3 Celia 58000 2 5 Linda 53000 3 2 James 32000 3 7 Jode 29000 4 7 rows selected. SQL>