**Summary**: in this tutorial, you will learn how to use the Oracle `NTILE()`

function to divide an ordered result set into a number of buckets and assign an appropriate bucket number to each row.

## Introduction to Oracle `NTILE()`

function

Oracle `NTILE()`

function is an analytical function that divides an ordered result set into a number of and assigns an appropriate bucket number to each row.

The following illustrates the syntax of the `NTILE()`

function:

Code language: SQL (Structured Query Language) (sql)`NTILE(expression) OVER ( [query_partition_clause] order_by_clause )`

In this syntax:

`expression`

The `expression`

is any valid expression evaluated to a positive integer.

The buck number is from 1 to the result of expression e.g., N. The number of rows in buckets can differ by at most 1. The remainder of the number of rows divided by buckets is allocated to each bucket, starting from the first bucket.

Suppose you have a result set that consists of 10 rows and you want to divide this result set into 4 buckets. First, 4 buckets will 2 rows are created. The remainder is 2 (10/4). These 2 buckets will be distributed to first and second buckets. As the result, the first and second buckets will have 2 + 1 = 3 rows. The last two buckets will have 2 rows.

The following picture illustrates the logic:

If the result of the expression is greater than the number of rows, then a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.

For example, if you have five rows and six buckets, each row will be assigned a bucket number from 1 to 6. The 7th bucket will be empty.

Notice that the `expression`

cannot contain any subquery or analytic function.

`partition_by_clause`

The `PARTITION BY`

clause divides the result sets into partitions to which the `NTILE`

function is applied:

Code language: SQL (Structured Query Language) (sql)`PARTITION BY expression1 [,expression2,..]`

`order_by_clause`

The `order_by_clause`

clause specifies the order of rows in each partition to which the `NTILE()`

is applied:

Code language: SQL (Structured Query Language) (sql)`ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]`

## Oracle `NTILE()`

function examples

We will use the `salesman_performance`

view for the demonstration:

```
CREATE OR REPLACE VIEW salesman_performance (
salesman_id,
year,
sales
) AS
SELECT
salesman_id,
EXTRACT(YEAR FROM order_date),
SUM(quantity*unit_price)
FROM
orders
INNER JOIN order_items USING (order_id)
WHERE
salesman_id IS NOT NULL
GROUP BY
salesman_id,
EXTRACT(YEAR FROM order_date);
```

Code language: SQL (Structured Query Language) (sql)

### Using Oracle `NTILE()`

function example

The following statement divides into 4 buckets the values in the `sales`

column of the `salesman_performance`

view from the year of 2017.

```
SELECT
salesman_id,
sales,
NTILE(4) OVER(
ORDER BY sales DESC
) quartile
FROM
salesman_performance
WHERE
year = 2017;
```

Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, the sales column has 9 values so the first bucket receives 3 rows. Other 3 buckets receive 2 rows.

### Using Oracle `NTILE()`

function with `PARTITION BY`

clause example

The following statement divides into 4 buckets the values in the `sales`

column of the `salesman_performance`

view in the year of 2016 and 2017:

```
SELECT
salesman_id,
sales,
year,
NTILE(4) OVER(
PARTITION BY year
ORDER BY sales DESC
) quartile
FROM
salesman_performance
WHERE
year = 2016 OR year = 2017;
```

Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this tutorial, you have learned how to use the Oracle `NTILE()`

function to divide an ordered result set into a number of buckets and assign an appropriate bucket number to each row.