Syntax
NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)
Purpose
NTILE() divides sorted rows of a partition into a specified number of groups that have the same size and returns the group number to each row. If expr is NULL, NULL is returned.
Examples
obclient> 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
obclient> 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
