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

to assign a unique sequential integer to each row in a result set.

## Introduction to Oracle`ROW_NUMBER()`

function

The `ROW_NUMBER()`

is an analytic function that assigns a sequential unique integer to each row to which it is applied, either each row in the partition or each row in the result set.

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

function:

`ROW_NUMBER() OVER ( [query_partition_clause] order_by_clause )`

In this syntax:

The `order_by_clause`

is required. It specifies the order of rows in each partition or in the whole result set. The `order_by_clause`

has the following form:

`ORDER BY expression1 [,expression2,...] [ASC | DESC ] [NULLS FIRST | LAST]`

The `query_partition_clause`

is optional. It distributes the rows into partitions to which the function is applied. If you omit the `query_partition_clause`

, the function will treat the whole result set as a single partition. The `query_partition_clause`

has the following form:

`PARTITION BY expression1 [,expression2, ...]`

## Oracle `ROW_NUMBER()`

examples

We’ll use the `products`

table from the sample database to demonstrate the `ROW_NUMBER()`

function.

### Oracle `ROW_NUMBER()`

simple example

The following statement returns the row number, product name and list price from the `products`

table. The row number values are assigned based on the order of list prices.

```
SELECT
ROW_NUMBER() OVER(
ORDER BY list_price DESC
) row_num,
product_name,
list_price
FROM
products;
```

The following picture shows the output:

This is a trivial example to just show how the `ROW_NUMBER()`

function works.

To effectively use the `ROW_NUMBER()`

function, you should use a subquery or a common table expression to retrieve row numbers for a specified range to get the top-N, bottom-N, and inner-N results.

### Using Oracle `ROW_NUMBER()`

function for pagination

The `ROW_NUMBER()`

function is useful for pagination in applications.

Suppose you want to display products by pages with the list price from high to low, each page has 10 products. To display the third page, you use the `ROW_NUMBER()`

function as follows:

```
WITH cte_products AS (
SELECT
row_number() OVER(
ORDER BY list_price DESC
) row_num,
product_name,
list_price
FROM
products
)
SELECT * FROM cte_products
WHERE row_num > 30 and row_num <= 40;
```

The output is:

In this example, the CTE used the `ROW_NUMBER()`

function to assign each row a sequential integer in descending order. The outer query retrieved the row whose row numbers are between 31 and 40.

### Using Oracle `ROW_NUMBER()`

function for the top-N query example

To get a single most expensive product by category, you can use the `ROW_NUMBER()`

function as shown in the following query:

```
WITH cte_products AS (
SELECT
row_number() OVER(
PARTITION BY category_id
ORDER BY list_price DESC
) row_num,
category_id,
product_name,
list_price
FROM
products
)
SELECT * FROM cte_products
WHERE row_num = 1;
```

Here is the output:

In this example:

- First, the
`PARTITION BY`

clause divided the rows into partitions by category id. - Then, the
`ORDER BY`

clause sorted the products in each category by list prices in descending order. - Next, the
`ROW_NUMBER()`

function is applied to each row in a specific category id. It re-initialized the row number for each category. - After that, the outer query selected the rows with row number 1 which is the most expensive product in each category.

For the consistent result, the query must return a result set with the deterministic order. For example, if two products had the same highest prices, then the result would not be consistent. It could return the first or second product.

To get more than one product with the same N-highest prices, you can use the `RANK()`

or `DENSE_RANK()`

function.

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

function to make useful queries such as inner-N, top-N, and bottom-N.