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() 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() OVER ( [query_partition_clause] order_by_clause )
In this syntax:
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]
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, ...]
We’ll use the
products table from the sample database to demonstrate the
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.
ROW_NUMBER() function for pagination
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.
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 BYclause divided the rows into partitions by category id.
- Then, the
ORDER BYclause 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.
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.