WindowSQL standard

NTILE

Splits rows into the given number of groups of roughly equal size and numbers the groups.

Syntax

NTILE(<group count>) OVER (ORDER BY <sort key>)
NTILE(<group count>) OVER (PARTITION BY <group> ORDER BY <sort key>)

Deals ordered rows into N groups and tells you which group each row is in: 1, 2, …, N. If the rows don't divide evenly, the extra ones go to the first groups. That's how you split into halves, thirds or quarters: “the more expensive half of orders”, “the top 25% by salary”.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

orders

id	created_at	amount
1	2026-07-03 10:15:00	120.50
2	2026-07-28 18:40:00	80.25
3	2026-08-09 09:05:00	199.99
4	2026-08-21 14:30:00	45.00
5	2026-09-02 11:00:00	64.75

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Two halves by salary

SELECT name, salary,

NTILE(2) OVER (ORDER BY salary DESC) AS half

FROM employees;

name	salary	half
Olivia	1350	1
Emma	1200	1
Sophia	1200	1
Liam	1000	2
Noah	950	2

5 rows don't split evenly into two groups, so the first group is bigger: 3 and 2.

Three groups of orders by amount

SELECT id, amount,

NTILE(3) OVER (ORDER BY amount DESC) AS bucket

FROM orders;

id	amount	bucket
3	199.99	1
1	120.50	1
2	80.25	2
5	64.75	2
4	45.00	3

Only the more expensive half of ordersAdvanced

SELECT id, amount

FROM (

SELECT id, amount,

NTILE(2) OVER (ORDER BY amount DESC) AS half

FROM orders

) split

WHERE half = 1;

id	amount
3	199.99
1	120.50
2	80.25

Watch out

The groups are equal in the number of rows, not in values. Equal amounts can end up in different groups, and group boundaries can fall anywhere.

If there are more groups than rows, some groups stay empty: NTILE(10) for 5 rows gives the numbers 1 to 5.

You can't use the result in the WHERE of the same query — calculate it in a subquery first and filter outside it.

Related functions

ROW_NUMBER RANK PERCENT_RANK

Learn more in the course

NTILE and percentiles