Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name, price,
       NTILE(4) OVER (ORDER BY price) AS price_quartile
FROM products;

Try it

SQL Quartiles of product prices ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Uneven sizes, and buckets inside a group ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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, NTILE still has to cut at exact row counts, so equal values can end up in different buckets. Use RANK() or CUME_DIST() when ties must stay together.
  • Always add a tie-breaker (such as the id) to the ORDER 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 CASE or width_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 DESC puts 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 use percentile_cont where 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.