Documentation

SQL Reference Guide

Previous Topic

Next Topic

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

  • The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
  • Only a single ranking function may be used in a statement.
  • Ranking functions are nondeterministic as they are computed for each generated result set.
  • Introduced in V12.0.1

See Also

TOCIndex