NTILE function
Syntax
NTILE( integer_expression ) OVER ( [PARTITION BY value_expression, ... [n]] order_by_clause;
Description
NTILE distributes the rows in an ordered partition into a specified integer_expression number of numbered groups. Group numbering starts at one. For each row, NTILE returns the number of the group the row belongs to. If the number of rows does not divide evenly into the number of groups specified, the beginning groups will have one more row in them than the ending groups such that the full number of rows is divided among the groups.
Example
Given this table definition.
create table ranking (c1 int, c2 int);
insert into ranking values (1,1);
insert into ranking values (1,2);
insert into ranking values (1,2);
insert into ranking values (1,3);
insert into ranking values (2,1);
insert into ranking values (2,1);
insert into ranking values (2,2);
insert into ranking values (2,3);
commit work;
select c1, c2, ntile(3) over (partition by c1 order by c2) from ranking;
C1 C2 NTILE(3.000000)
-- -- --------------
1 1 1
1 2 1
1 2 2
1 3 3
2 1 1
2 1 1
2 2 2
2 3 3
Notes
See Also