How to Use NTILE in PostgreSQL: Syntax and Examples

In PostgreSQL, use the NTILE(n) window function to divide rows into n roughly equal groups, assigning a group number to each row. It is used with OVER() clause, often combined with ORDER BY to define the row order before grouping.

Syntax

The NTILE(n) function divides the result set into n groups and assigns a group number to each row. It requires a window specification with OVER(), where you usually specify ORDER BY to sort rows before grouping.

• n: Number of groups to divide the rows into.
• OVER(): Defines the window for the function.
• ORDER BY: Sorts rows before assigning group numbers.

sql

NTILE(n) OVER (ORDER BY column_name)

Example

This example shows how to divide a list of sales amounts into 4 groups (quartiles) using NTILE(4). Each row gets a group number from 1 to 4 based on the sales amount order.

sql

CREATE TEMP TABLE sales(amount INT);
INSERT INTO sales VALUES (100), (200), (300), (400), (500), (600), (700), (800);

SELECT amount, NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales
ORDER BY amount;

Output

 amount | quartile 
--------+----------
    100 |        1
    200 |        1
    300 |        2
    400 |        2
    500 |        3
    600 |        3
    700 |        4
    800 |        4

Common Pitfalls

Common mistakes when using NTILE include:

• Not specifying ORDER BY inside OVER(), which leads to unpredictable group assignments.
• Using NTILE with a number larger than the number of rows, which results in some groups being empty.
• Confusing NTILE with ROW_NUMBER() or RANK(), which serve different purposes.

sql

/* Wrong: No ORDER BY, unpredictable groups */
SELECT amount, NTILE(3) OVER () AS group_no FROM sales;

/* Right: With ORDER BY for consistent grouping */
SELECT amount, NTILE(3) OVER (ORDER BY amount) AS group_no FROM sales;

Quick Reference

Term	Description
NTILE(n)	Divides rows into n groups, assigning group numbers
OVER()	Defines the window for the function
ORDER BY	Sorts rows before grouping
Group Number	Integer from 1 to n assigned to each row
Empty Groups	Can occur if n > number of rows

Key Takeaways

Use NTILE(n) with OVER(ORDER BY column) to split rows into n groups based on order.

Always include ORDER BY inside OVER() for predictable group assignments.

NTILE assigns group numbers from 1 to n, roughly evenly distributing rows.

Avoid setting n larger than the number of rows to prevent empty groups.

NTILE is different from ROW_NUMBER() and RANK(), so choose based on your need.