ROW_NUMBER

The ROW_NUMBER() function is a window function in SQL that assigns a unique sequential integer to rows within a result set. Each row in the partition gets a number starting from 1, based on the order you specify.

---

Basic Syntax

ROW_NUMBER() OVER (
    [PARTITION BY column_name]
    ORDER BY column_name [ASC|DESC]
)

• PARTITION BY: Optional. Divides the result set into partitions and resets the row number for each partition.
• ORDER BY: Required. Specifies the order in which the row numbers are assigned.
• ASC / DESC: Determines ascending or descending order.

---

Simple Example

Suppose we have a table Employees:

EmployeeID	Name	Department
101	Alice	Sales
102	Bob	Sales
103	Carol	HR
104	David	HR

Assign row numbers to all employees:

SELECT
    Name,
    Department,
    ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
FROM Employees;

Result:

Name	Department	RowNum
Alice	Sales	1
Bob	Sales	2
Carol	HR	3
David	HR	4

---

Using PARTITION BY

Assign row numbers per department:

SELECT
    Name,
    Department,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Name) AS RowNum
FROM Employees;

Result:

Name	Department	RowNum
Alice	Sales	1
Bob	Sales	2
Carol	HR	1
David	HR	2

---

Common Use Cases

1. Finding duplicates – Keep only the first occurrence.
2. Paging results – Retrieve rows in specific batches for web pages.
3. Ranking – Assign sequential ranks without gaps.

---

Key Points

• ROW_NUMBER() always starts at 1 for each partition.
• Requires ORDER BY; otherwise, the numbering will be unpredictable.
• Can be combined with WHERE or CTE to filter specific rows.

---