PostgreSQL 18 reference · Window Functions

PostgreSQL NTILE() Function

Split the rows of a window into N groups of nearly equal size and return each row's group number.

PostgreSQL 18 Since 8.4

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

ntile(num_buckets) OVER (PARTITION BY ... ORDER BY ...)

Parameters

Parameter	Description
`num_buckets`	The number of groups.

Return value

integer from 1 to N.

Examples

Salary quartiles

SELECT quartile, count(*) AS people, min(salary) AS from_salary
FROM (SELECT salary, ntile(4) OVER (ORDER BY salary) AS quartile FROM employees) AS q
GROUP BY quartile
ORDER BY quartile;

Output

 quartile | people | from_salary
----------+--------+-------------
        1 |      3 |       65000
        2 |      3 |      112000
        3 |      2 |      150000
        4 |      2 |      210000
(4 rows)

Run this on your computer — see “Get Started” for setup.

Two halves

SELECT name, ntile(2) OVER (ORDER BY price) AS half
FROM products
WHERE category_id = 2;