Oracle ROW_NUMBER

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
)Code language: SQL (Structured Query Language) (sql)

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]
Code language: SQL (Structured Query Language) (sql)

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, ...]Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 a 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.