NTILE function

The NTILE function is an OLAP ranking function that classifies the rows in each partition into N ranked categories, called tiles, where each category includes an approximately equal number of rows.

Syntax

(explicit id ntilel001) ntilel001

NTILE function

NTILE (unsigned)
<OVER clause for Ranking functions>[]
Element Description Restrictions Syntax
unsigned Unsigned integer that specifies how many categories, or tiles, to rank Cannot be zero Literal integer

Usage

The number of ranked categories, or tiles, is set by the unsigned integer argument to the function, and on the ORDER BY expression in the OVER clause.

For example, the following ranking function expression returns percentile rankings from 1 to 100 for the values in the dollars column for all the rows in the query result set:
NTILE(100) OVER(ORDER BY dollars)

When the argument is 4, the returned values sort the rows in each partition that the OVER clause defines into four quartiles. When a set of values is not divisible by the specified integer argument, the NTILE function puts leftover rows in the lower-ranked tiles.

Example: NTILE function

The following query ranks employees in departments by employee salary, and calculates the tile number of 1 through 5 for each department. 

SELECT name, salary,
   NTILE(5) OVER (PARTITION BY dept ORDER BY salary)
FROM employee;
         name      salary        (ntile)
         John      35,000              1
         Jack      38,400              1
        Julie      41,200              2
        Manny      45,600              2
        Nancy      47,300              3
          Pat      49,500              4
          Ray      51,300              5

The salaries are ordered from lowest to highest because the default ordering direction for the ORDER BY clause is ascending. If you include the DESC keyword in the ORDER BY clause, the salaries are ordered from highest to lowest.

1 See OVER clause for OLAP window expressions