SQL Reference ยท Window Functions
SQL NTILE() function
Reference for SQL NTILE(n): divide ordered rows into n buckets such as quartiles or deciles, how uneven sizes are handled, per-group buckets and tie behavior.
NTILE(n) splits the ordered rows of a window into n buckets that are as equal in size as possible and returns the bucket number, from 1 to n, for each row. Use it for quartiles (NTILE(4)), deciles (NTILE(10)), percentiles (NTILE(100)) and any "top 20 percent" style grouping.
The buckets are filled by row count, not by value. When the rows cannot be divided evenly, the first buckets get one row more than the later ones. Rows with equal sort values can land in different buckets, because NTILE does not look at ties.
SELECT name, price, NTILE(4) OVER (ORDER BY price) AS price_quartile FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
NTILE(buckets) OVER ( [ PARTITION BY expression [, ...] ] ORDER BY expression [ ASC | DESC ] [, ...] )
| Part | Description |
|---|---|
buckets |
A positive integer: how many groups to create. Use a constant; many databases also accept an expression that does not depend on the row. |
PARTITION BY |
Optional. The rows of each partition are divided separately. |
ORDER BY |
Decides which rows fall in the early buckets. |
Returns: an integer from 1 to buckets. If a partition has fewer rows than buckets, only the numbers 1 to the row count are used.
Size rule: for r rows and n buckets, every bucket gets r / n rows (rounded down), and the first r mod n buckets get one more.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
NTILE(n) |
3.25+ | 8.4+ | 8.0+ | 2005+ | Yes |
OVER without ORDER BY |
Allowed | Allowed | Allowed | Error | Error |
| Larger buckets first | Yes | Yes | Yes | Yes | Yes |
-- PostgreSQL, Oracle: value-based alternative that respects ties SELECT name, price, width_bucket(price, 0, 200, 4) AS price_range -- PostgreSQL; Oracle has WIDTH_BUCKET too FROM products;
Usage notes
- Equal rows may be split. If many rows share a price,
NTILEstill has to cut at exact row counts, so equal values can end up in different buckets. UseRANK()orCUME_DIST()when ties must stay together. - Always add a tie-breaker (such as the
id) to theORDER BY, or the assignment of tied rows is arbitrary. - Buckets are by count, not by range. A quartile bucket is not "a quarter of the price range". Compare with
CASEorwidth_bucket()for range-based bins. - Fewer rows than buckets gives some buckets no rows; they are simply missing, not zero-filled.
- Direction matters.
ORDER BY salary DESCputs the highest earners in bucket 1. Say which way your tiers go in the column name. - Not a statistical percentile.
NTILE(100)gives a rough percentile bucket per row. For exact percentile values usepercentile_contwhere available, or compute from sorted rows. - Filter in an outer query. To keep, say, the top quartile, wrap in a subquery and filter
quartile = 1.