You are viewing an older version of this section. View current production version.
Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

NTILE

Ranking function. Divides the input into the specified number of buckets and returns the bucket number of the current row.

Syntax

NTILE ( bucket_count ) OVER (
    [PARTITION BY (col | expr), ...] 
    [ORDER BY (col | expr), ...]
)

Arguments

  • bucket_count: an integer.

Return Type

An integer

Examples

memsql> create table example (a int, b int);

memsql> insert into example values(1,2),(2,2),(3,3),(4,3);

memsql> insert into example values(3,2),(1,1),(3,1);

memsql> select a,b, ntile(3) over(order by a) from example;
+------+------+---------------------------+
| a    | b    | ntile(3) over(order by a) |
+------+------+---------------------------+
|    1 |    2 |                         1 |
|    1 |    1 |                         1 |
|    2 |    2 |                         1 |
|    3 |    3 |                         2 |
|    3 |    2 |                         2 |
|    3 |    1 |                         3 |
|    4 |    3 |                         3 |
+------+------+---------------------------+