NTILEFunctions
Buy Me A Coffee

Divide rows in a window partition into a specified number of ranked buckets (tiles) as evenly as possible, returning the bucket number for each row.

Divides rows into N roughly equal groups and assigns a bucket number (1 to N) to each row. Useful for creating quartile, quintile, or decile buckets for analysis and bucketed sampling.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 NTILE(n) added in MySQL 8.0. Distributes rows into n buckets. If rows don't divide evenly, earlier buckets get one extra row. NTILE(100) = percentile bucket.
PostgreSQL ✓ Supported 8.4 Standard NTILE(n). Supports the WINDOW clause shorthand for reusing window definitions.
SQL Server ✓ Supported 2005 Standard NTILE(n). Available since SQL Server 2005.
Oracle ✓ Supported 8i Standard NTILE(n). Available since 8i.
SQLite ✓ Supported 3.25.0 NTILE(n) added in SQLite 3.25.0.

Details

NTILE is universally supported across all engines (post MySQL 8.0). The bucket distribution rule for uneven splits is consistent: earlier buckets get the extra row. NTILE(100) gives approximate percentile buckets; for true percentiles use PERCENTILE_CONT() WITHIN GROUP.

Standard Syntax

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2005 Oracle: Since 8i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

NTILE(n) added in MySQL 8.0. Distributes rows into n buckets. If rows don't divide evenly, earlier buckets get one extra row. NTILE(100) = percentile bucket.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile, NTILE(10) OVER (ORDER BY score DESC) AS decile, NTILE(100) OVER (ORDER BY score DESC) AS percentile FROM students; -- With partition: SELECT name, dept, salary, NTILE(3) OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_tier FROM employees;

PostgreSQL

Standard NTILE(n). Supports the WINDOW clause shorthand for reusing window definitions.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students; -- Reusing window definition: SELECT name, score, NTILE(4) OVER w AS quartile, NTILE(10) OVER w AS decile FROM students WINDOW w AS (ORDER BY score DESC);

SQL Server

Standard NTILE(n). Available since SQL Server 2005.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile, NTILE(10) OVER (ORDER BY score DESC) AS decile FROM students;

Oracle

Standard NTILE(n). Available since 8i.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;

SQLite

NTILE(n) added in SQLite 3.25.0.

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;