ROW_NUMBER

The ROW_NUMBER function in Oracle is an analytic function that assigns a unique sequential number to rows within a result set. The number is based on the ordering specified in the ORDER BY clause. It is commonly used for pagination, ranking, and filtering in SQL queries.

1. What is ROW_NUMBER?

The ROW_NUMBER function assigns a unique number to each row in the result set. It starts from 1 for the first row and increments by 1 for each subsequent row. This is particularly useful when you need to uniquely identify or order rows.

• Unique for each row: Even if rows contain identical values, ROW_NUMBER will still assign a unique number to each row.
• Used with ORDER BY: The numbering is determined by the order of rows as specified in the ORDER BY clause.

 

2. Basic Syntax

The basic syntax of the ROW_NUMBER function is:

SELECT column1, column2,

       ROW_NUMBER() OVER (ORDER BY column_name) AS row_num

FROM table_name;

• ROW_NUMBER(): The function that generates the row numbers.
• OVER: Specifies the window for the function (i.e., how the rows are divided and ordered).
• ORDER BY column_name: Defines the order of the rows before the row numbers are assigned.
• AS row_num: An alias for the generated row number column.

 

3. How ROW_NUMBER Works

ROW_NUMBER works by:

1. Ordering the data based on the columns specified in the ORDER BY clause.
2. Assigning sequential numbers to each row based on the specified order.

• Starting number: The numbering always starts at 1.
• Increment: The row number increments by 1 for each subsequent row, even for rows with the same values.

For example, if you have a table of employees and you want to assign row numbers based on their salaries, Oracle will rank the rows from 1 upwards according to the salary order.

 

4. Example of ROW_NUMBER

Consider the following dataset of employees:

Employee	Salary
Alice	1000
Bob	1500
Carol	2000
Dave	2500
Eve	3000

Query:

SELECT Employee, Salary,

       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Row_Number

FROM Employees;

Result:

Employee	Salary	Row_Number
Eve	3000	1
Dave	2500	2
Carol	2000	3
Bob	1500	4
Alice	1000	5

Here, the ROW_NUMBER function assigns a unique number to each row based on the Salary in descending order.

 

5. Using ROW_NUMBER with PARTITION BY

You can use ROW_NUMBER with the PARTITION BY clause to generate row numbers for each group of rows within a dataset. This is helpful when you want to restart the row numbering for different partitions (e.g., employees within different departments).

Example with PARTITION BY:

SELECT Department, Employee, Salary,

       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Row_Number

FROM Employees;

In this query, the row numbering will restart for each department. Employees within the same department will have their row numbers assigned based on the Salary order.

 

6. Use Cases of ROW_NUMBER

• Pagination: You can use ROW_NUMBER to implement pagination in your SQL queries. This is useful for displaying a set number of rows per page (e.g., showing 10 rows at a time).
• Deduplication: ROW_NUMBER can be used to assign unique row numbers to duplicates and then filter out the duplicate rows, keeping only the first occurrence.
• Ranking: Although ROW_NUMBER does not handle ties like RANK or DENSE_RANK, you can use it for sequential ranking where ties don’t matter.
• Top N Queries: When you need to retrieve the top N rows from a dataset, ROW_NUMBER can be used to assign row numbers and then filter for rows where the row number is less than or equal to N.

 

7. Example of ROW_NUMBER for Pagination

To retrieve 10 rows per page and display page 2 of a dataset, you can use ROW_NUMBER like this:

Query:

SELECT *

FROM (

    SELECT Employee, Salary,

           ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Row_Number

    FROM Employees

)

WHERE Row_Number BETWEEN 11 AND 20;

This query assigns row numbers starting from 1 based on Salary, then filters to get rows 11 to 20, simulating the second page of a paginated result.

 

8. Performance Considerations

• Sorting: ROW_NUMBER requires sorting the data, which can impact performance, especially for large datasets. Indexing the columns used in the ORDER BY clause can help optimize performance.
• Windowing Function: Since ROW_NUMBER is a window function, it can be computationally expensive if used with large data volumes or without proper indexing.

 

9. ROW_NUMBER vs. RANK and DENSE_RANK

• ROW_NUMBER: Assigns a unique sequential number to each row. Even if rows have the same value, each will get a different row number.
• RANK: Assigns the same rank to rows with the same value but skips the next rank(s) for ties. For example, if two rows are tied for rank 1, the next rank will be 3.
• DENSE_RANK: Similar to RANK, but does not skip ranks for ties. The next rank will be 2, not 3.

ROW_NUMBER is used when you need a unique, sequential number for every row, regardless of ties.

 

10. Limitations of ROW_NUMBER

• No handling of ties: Unlike RANK or DENSE_RANK, ROW_NUMBER does not account for ties. Each row receives a unique row number, even if the rows have identical values.
• Dependent on sorting: The results of ROW_NUMBER depend on the ORDER BY clause. Without sorting, the row numbers would not follow a meaningful order.

 

11. Conclusion

The ROW_NUMBER function is a powerful analytic tool in Oracle for assigning unique, sequential numbers to rows within a result set. It is particularly useful in scenarios like pagination, deduplication, and ranking where unique row identification is necessary. By using PARTITION BY and ORDER BY, you can further tailor the row numbering to suit different use cases.