NTILE

Functions
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

Filter by Database
SQL NTILE Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 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 Native 8.4 Standard NTILE(n). Supports the WINDOW clause shorthand for reusing window definitions.
SQL Server Native 2005 Standard NTILE(n). Available since SQL Server 2005.
Oracle Native 8i Standard NTILE(n). Available since 8i.
SQLite Native 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: Native since 8.0 PostgreSQL: Native since 8.4 SQL Server: Native since 2005 Oracle: Native since 8i SQLite: Native since 3.25.0

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

Standard NTILE(n). Available since 8i.

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

SQLite Native syntax

NTILE(n) added in SQLite 3.25.0.

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