ROW_NUMBER Function in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Row_Number Function in Oracle with Examples

In this article, I will discuss the Row_Number Function in Oracle with Examples. Please read our previous article, which gives an overview of the Analytical Functions in Oracle. At the end of this article, you will understand what exactly the ROW_NUMBER function is and when and how to use the Row Number Function in Oracle with real-time examples.

ROW_NUMBER Function in Oracle:

The ROW_NUMBER function in Oracle is analytic or, you can say, a ranking function that is used when we want to return a unique sequential number starting from 1 for record in the result set. The ROW_NUMBER() function is a built-in function in Oracle that will assign a unique sequential integer number to each row starting from 1 within a partition of a result set. The row number always starts with 1 for the first row in each partition and then increases by 1 for the next row onwards for each partition. The syntax to use the ROW_NUMBER function in Oracle is given below.

In Oracle, the ROW_NUMBER() function is a powerful analytic function that assigns a unique integer value to each row within the result set of a query. This function is commonly used for tasks that involve ranking or ordering data. It is often combined with other analytic functions like PARTITION BY and ORDER BY to perform more complex analytical operations on your data.

Parameters:

1. PARTITION BY value_expression: This optional clause divides the result set into partitions or groups based on one or more columns. The ROW_NUMBER() function will restart its count for each partition. If you don’t specify PARTITION BY, it treats the entire result set as a single partition.
2. ORDER_BY_CLAUSE: This mandatory clause specifies the column(s) by which the rows should be ordered within each partition. The ROW_NUMBER() function assigns row numbers based on the order defined here. You can specify one or more columns and specify whether you want ascending (ASC) or descending (DESC) order.

Examples to Understand the ROW_NUMBER Function in Oracle

Let us understand the ROW_NUMBER function in Oracle with examples. We will use the following Employees table to understand the ROW_NUMBER function.

Please use the following SQL Script to create and populate the Employees table with the required sample data.

CREATE TABLE Employees
(
     ID INT PRIMARY KEY,
     Name VARCHAR2(20),
     Department VARCHAR(20),
     Salary int
);

INSERT INTO Employees Values (1001, 'Sambit', 'IT', 15000);
INSERT INTO Employees Values (1002, 'Santosh', 'IT', 35000);
INSERT INTO Employees Values (1003, 'Manoj', 'HR', 15000);
INSERT INTO Employees Values (1004, 'Rakesh', 'Payroll', 35000);
INSERT INTO Employees Values (1005, 'Hina', 'IT', 42000);
INSERT INTO Employees Values (1006, 'Bikash', 'HR', 15000);
INSERT INTO Employees Values (1007, 'Priya', 'HR', 67000);
INSERT INTO Employees Values (1008, 'Preety', 'Payroll', 67000);
INSERT INTO Employees Values (1009, 'Priyanka', 'Payroll', 55000);
INSERT INTO Employees Values (1010, 'Anurag', 'Payroll', 15000);
INSERT INTO Employees Values (1011, 'Rishav', 'HR', 55000);

ROW_NUMBER Function without PARTITION BY Clause in Oracle:

Let us first see an example of the ROW NUMBER function without using the PARTITION BY Clause in Oracle. As we already discussed, if we did not specify the PARTITION BY Clause in Oracle, then the ROW_NUMBER function will treat the whole result set as a single partition. As a result, the ROW_NUMBER function will provide a consecutive sequence numbering for all the rows present in the result set based on the column(s) specified in the order by clause.

For a better understanding, please have a look at the below example, where we used the ROW_NUMBER function without using the PARTITION BY clause.

SELECT Id, Name, Department, Salary,
ROW_NUMBER() OVER (ORDER BY Department) AS RowNumber
FROM Employees;

Once you execute the above query, you will get the following output. If you observe the below output, you can see that there is no partition, and hence, all the rows are assigned with consecutive sequence numbering starting from 1 to 12 based on the Department column.

Row_Number Function with PARTITION BY Clause in Oracle:

Let us see an example of the ROW_NUMBER function with PARTITION BY Clause in Oracle. When we specify the PARTITION BY Clause, then the data is going to be partitioned based on the column we specify in the PARTITION BY clause. Please have a look at the following example code to understand this better. As you can see, here, we have specified the Department in the Partition By clause and the Name in the Order By clause.

SELECT Id, Name, Department, Salary,
ROW_NUMBER() OVER 
(
 PARTITION BY Department
 ORDER BY Name
) AS RowNumber
FROM Employees;

When you execute the above query, you will get the following output. In our Employees table, we have three departments. So, the Partition by Clause will divide all the rows into three partitions (IT, HR, and Payroll). Then, on each partition, the data is sorted based on the Name column. Finally, the ROW_NUMBER function gives a unique sequence integer number starting from 1 to each partition. As we have three partitions, each partition will start from 1, as you can see in the image below.

Finding the Top N Rows of every group using the Row Number function in Oracle

To understand how to find the top n rows of every group, we are going to use the same Employees table. As you can see, the Employees table contains three department employees. We can use the ROW_NUMBER() function to find the top N rows for every group. For example, let us see how to find the top two employees of each department based on the highest salary. The following SQL query uses the ROW_NUMBER() function to find the top two employees of each department based on the highest salary.

WITH HighestSalaryEmployees
AS 
(
    SELECT Id, Name, Department, Salary,
    ROW_NUMBER() OVER 
    ( 
        PARTITION BY Department
        ORDER BY Salary DESC
    ) RowNumber
    FROM Employees
)
SELECT Id, Name, Department, Salary 
FROM HighestSalaryEmployees
WHERE RowNumber <= 2;

Once you execute the above query, you will get the following desired output.

Pagination using ROW_NUMBER() Function in Oracle

As the ROW_NUMBER() function in Oracle assigns a unique integer number to each row in the result, we can use it for pagination. For example, we need to display a list of employees with 5 employees per page. To get all the employees for the second page, we need to use the following SQL query.

SELECT * FROM 
(
    SELECT Id, Name, Department, Salary,
    ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM Employees
) temp
WHERE RowNumber BETWEEN 6 AND 10;

Once you execute the above query, you will get the following desired output.

Deleting Duplicate Records in Oracle using ROW_NUMBER Function:

To understand this, we are going to use the following Employee table. Here, intentionally, we have inserted some duplicate data into the Employee table.

Please use the following SQL Script to create the Employee table and populate the Employee table with the required sample data.

CREATE TABLE Employee
(
     ID INT PRIMARY KEY,
     Code VARCHAR(20),
     Name VARCHAR(20),
     Department VARCHAR2(10),
     Salary int
);

INSERT INTO Employee Values (1, 'EMP1001', 'Rakesh', 'IT', 15000);
INSERT INTO Employee Values (2, 'EMP1001', 'Rakesh', 'IT', 15000);
INSERT INTO Employee Values (3, 'EMP1002', 'Priya', 'HR', 25000);
INSERT INTO Employee Values (4, 'EMP1002', 'Priya', 'HR', 25000);
INSERT INTO Employee Values (5, 'EMP1002', 'Priya', 'HR', 25000);
INSERT INTO Employee Values (6, 'EMP1003', 'Anurag', 'HR', 35000);
INSERT INTO Employee Values (7, 'EMP1003', 'Anurag', 'HR', 35000);
INSERT INTO Employee Values (8, 'EMP1003', 'Anurag', 'HR', 35000);
INSERT INTO Employee Values (9, 'EMP1003', 'Anurag', 'HR', 35000);

Now, we need to delete the duplicate records from the Employee table. Once we delete the duplicate records, our Employee table should have the following three records.

The following SQL query will delete the duplicate records from the Employee table. Here, the PARTITION BY clause divides the result set into partitions based on the Employee Code column. That means it will create multiple partitions based on the Employee Code column value and give sequential numbers starting from 1 for each partition. Then, the DELETE statements will delete the records from each partition where the RowNumber is greater than 1. It means it will keep only one record from each partition and delete the rest of all.

DELETE FROM EMPLOYEE WHERE ROWID IN
( 
       SELECT RWID FROM (SELECT ROWID RWID,      
       ROW_NUMBER() OVER ( PARTITION BY CODE ORDER BY ID) ROWNUMBER
       FROM   EMPLOYEE )
       WHERE  ROWNUMBER>1
);

When you execute the above query, you will get the following message saying 6 rows deleted.

When to use the Row_Number Function in Oracle?

You should use the ROW_NUMBER() function in Oracle when assigning a unique row number to each row in a result set, typically to rank or order data within specific groups or partitions. Here are some common scenarios in which you might want to use the ROW_NUMBER() function:

• Ranking Data: You can use ROW_NUMBER() to rank rows based on a specific column’s values. For example, you might want to rank employees by their salary or students by their exam scores.
• Pagination: When implementing pagination in your application, you can use ROW_NUMBER() to assign row numbers to the result set and then filter the needed rows based on a specified range. This is often used in web applications to display a limited number of records per page.
• Unique Identifiers: When your data doesn’t have a unique identifier, you need to create one based on the result set order.
• Top-N Queries: To find the top or bottom N records within a category. For instance, you might want to find the top 3 salespersons in each region.
• Grouping and Aggregating Data: Sometimes, you want to perform calculations or aggregations on specific data groups. You can use ROW_NUMBER() to assign row numbers within each group defined by the PARTITION BY clause.
• Identifying Duplicates: ROW_NUMBER() can help you identify duplicate (and possibly remove) rows in a table by assigning the same row number to duplicate rows. You can then filter or delete these duplicates based on the assigned row numbers.
• Analytical Queries: When you need to perform complex analytical queries, ROW_NUMBER() can be a crucial tool. You can combine it with other analytic functions to compute various statistics and summaries of your data.
• Custom Sorting: If you need to sort data in a custom order that is not achievable with a simple ORDER BY clause, you can use ROW_NUMBER() to assign custom sorting criteria and then order the results accordingly.
• Record Versioning: In scenarios with historical data or audit records, you may use ROW_NUMBER() to version entries based on a timestamp or transaction ID.

So, the ROW_NUMBER() function is used when we need to generate a sequence of numbers for each row in a result set, and this sequence is significant for your subsequent operations, such as ranking, pagination, grouping, or custom sorting. It provides a way to perform these tasks while maintaining good performance, as it is often more efficient than alternative methods like self-joins or subqueries that do not use window functions.

In the next article, I will discuss the RANK Function in Oracle with Examples. Here, in this article, I try to explain the need and use of the ROW NUMBER Function in Oracle with Examples. I hope you enjoy this ROW NUMBER function article.