NTILE

Syntax

NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)

Description

Divides the sorted rows in the partition into groups of approximately equal size as specified by the specified number of groups and returns the group number to each row. If expr is NULL, it returns NULL.

Examples

CREATE TABLE employees(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
COMMIT;
Query OK, 0 rows affected (0.001 sec)

SELECT LAST_NAME, NTILE(10) OVER(PARTITION BY JOB_ID ORDER BY SALARY) NTL FROM employees;
+-----------+------+
| LAST_NAME | NTL  |
+-----------+------+
| JIM       |    1 |
| TOM       |    1 |
| MIKE      |    2 |
| LILY      |    3 |
+-----------+------+
4 rows in set (0.003 sec)