What is ROW_NUMBER()?

ROW_NUMBER() is an analytic function in Oracle SQL that assigns a unique sequential number to each row within a result set or partition, based on a specific order.

Unlike the ROWNUM pseudocolumn (which is assigned before sorting), ROW_NUMBER() is assigned after the ORDER BY clause, which makes it more reliable for pagination, ranking, and ordered row selection.

Syntax

ROW_NUMBER() OVER (ORDER BY column_name)

Optional Partitioning:

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY another_column)

Key Points

Feature	Details
Type	Analytic function
Starts from	1 for each partition
Can reset per group?	Yes, with PARTITION BY
Order matters?	Yes, you must specify ORDER BY
Deterministic?	Yes — unlike ROWNUM, it’s order-aware

Example 1: Assign Row Numbers to All Employees by Salary

SELECT 
  employee_id, 
  first_name, 
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Result:

employee_id	first_name	salary	row_num
102	Sarah	15000	1
101	John	12000	2
103	Linda	10000	3

ROW_NUMBER() assigns unique numbers after sorting by salary DESC.

Example 2: Top Paid Employee in Each Department

SELECT *
FROM (
  SELECT 
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
WHERE rn = 1;

Explanation:

• The PARTITION BY clause resets row number for each department.
• The outer query filters to keep only the top-paid employee per department.

Pagination with ROW_NUMBER()

Example: Get records from 11 to 20

SELECT *
FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
  FROM employees e
)
WHERE rn BETWEEN 11 AND 20;

This is a common pattern for paginated queries:

• Inner query assigns row numbers based on the desired sort order.
• Outer query filters based on range.

Compare: ROW_NUMBER() vs RANK() vs DENSE_RANK()

Function	Handles Ties?	Skips Numbers?	Use Case
ROW_NUMBER()	No — all rows get unique numbers	Yes	Pagination
RANK()	Yes	Yes	Ranking with gaps
DENSE_RANK()	Yes	No	Ranking without gaps

Example (Same Salary):

SELECT
  employee_id,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Real-World Use Cases

Use Case	How ROW_NUMBER() Helps
Pagination	Get rows between N and M efficiently
Top-N per group	Select top 1 product/customer/order in each group
De-duplication	Keep only the first row based on sorting
Ranking	Assign sequential ranks with order control

Limitations

Limitation	Explanation
Must use subquery to filter ROW_NUMBER()	You can’t use it directly in WHERE
Needs ORDER BY	Required for row numbering to work
Not compatible with older Oracle versions	Requires Oracle 8i or newer

Summary

Feature	Description
Name	ROW_NUMBER()
Type	Analytic function
Purpose	Assign sequential row number based on sort
Partitions supported?	Yes
Order-aware?	Yes
Use cases	Pagination, ranking, top-N, de-duplication
Better than ROWNUM?	Yes — because it’s predictable and flexible

---

Bonus: Oracle 12c+ Pagination (with FETCH)

If you’re on Oracle 12c or later, you can also do pagination like this:

SELECT *
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

But ROW_NUMBER() remains useful when:

• You need to rank within partitions.
• You want fine control over row numbers.