1. Core Concept & Syntax Architecture
ROW_NUMBER() assigns a unique sequential number (starting from 1) to each row in the result set. It is an analytical window function evaluated at query execution time—it does NOT permanently modify the underlying database table.
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
Generates the sequential 1, 2, 3... integer count.
Dictates the exact sort order in which rows receive sequential numbers.
2. 🔥 Live Interactive — Basic ROW_NUMBER Lab
Observe how ROW_NUMBER() OVER (ORDER BY salary DESC) indexes the employee compensation roster:
| ROW_NUMBER() | Emp ID | Employee Name | Department | Salary ₹ |
|---|---|---|---|---|
| #1 | 105 | Arjun | Sales | ₹72,000 |
| #2 | 103 | Rahul | Sales | ₹72,000 |
| #3 | 106 | Sneha | Finance | ₹70,000 |
| #4 | 102 | Priya | Finance | ₹65,000 |
| #5 | 104 | Neha | HR | ₹60,000 |
| #6 | 101 | Amit | Sales | ₹55,000 |
3. ORDER BY Controls Number Assignment
Toggle between DESC (highest salary = 1) and ASC(lowest salary = 1) to confirm that ROW_NUMBER() reflects the query's ordering:
4. 🔥 Live Interactive — PARTITION BY
When PARTITION BY department is added, row numbering resets back to 1 for every distinct department group:
| Department | Employee Name | Salary ₹ | ROW_NUMBER() Output |
|---|---|---|---|
| Finance | Sneha | ₹70,000 | — |
| Finance | Priya | ₹65,000 | — |
| HR | Neha | ₹60,000 | — |
| Sales | Rahul | ₹72,000 | — |
| Sales | Arjun | ₹72,000 | — |
| Sales | Amit | ₹55,000 | — |
5. 🔥 Top N Per Group Using CTEs
The standard enterprise pattern for finding the highest-paid employee (or Top N) per department wraps ROW_NUMBER inside a Common Table Expression (CTE):
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT * FROM ranked_employees WHERE rn <= 1;
| Department | Rank (rn) | Employee Name | Salary ₹ |
|---|---|---|---|
| Finance | #1 | Sneha | ₹70,000 |
| HR | #1 | Neha | ₹60,000 |
| Sales | #1 | Rahul | ₹72,000 |
6. Handling Tied Values & Deterministic Ordering
In the Sales department, both Rahul and Arjun earn ₹72,000. ROW_NUMBER() always assigns different numbers (1 and 2). To guarantee deterministic results across query runs, add a secondary tie-breaker column:
Non-deterministic (database internal row ordering decides #1 vs #2).
7. 🔥 Live Data Deduplication Challenge
Customer records contain duplicate email updates. Keep only the latest record per email:
| Email (PARTITION BY) | Customer Name | Updated Timestamp | rn (ORDER BY updated_at DESC) |
|---|---|---|---|
| rohan@tech.com | Rohan Verma | 2026-01-10 09:15:00 | rn = 2 (PRUNED) |
| rohan@tech.com | Rohan V. | 2026-08-25 14:30:00 | rn = 1 (KEPT) |
| ananya@fin.com | Ananya Roy | 2026-02-14 11:20:00 | rn = 2 (PRUNED) |
| ananya@fin.com | Ananya Roy-Mehta | 2026-07-19 18:45:00 | rn = 1 (KEPT) |
| vikram@biz.com | Vikram Singh | 2026-05-01 10:00:00 | rn = 1 (KEPT) |
8. 🔥 Live Challenge — Top 3 Per Category
Retrieve the 3 most expensive products in each category:
| Category | Rank | Product Name | Price ₹ |
|---|---|---|---|
| Electronics | #1 | MacBook Pro M3 | ₹1,69,000 |
| Electronics | #2 | Dell XPS 15 | ₹1,45,000 |
| Electronics | #3 | iPad Pro 13 | ₹1,19,000 |
| Electronics | #4 | AirPods Max | ₹59,900 |
| Electronics | #5 | Logitech MX Keys | ₹12,500 |
| Furniture | #1 | Herman Miller Chair | ₹98,000 |
| Furniture | #2 | Steelcase Gesture | ₹89,000 |
| Furniture | #3 | Ergonomic Desk | ₹35,000 |
| Furniture | #4 | Monitor Arm Dual | ₹8,500 |
9. Common Mistake: Direct WHERE Filtering
In SQL logical execution order, WHERE evaluates before SELECT (Window functions). Therefore, the database does not know what ROW_NUMBER() is at the WHERE stage.
Solution: Compute ROW_NUMBER() inside a CTE or subquery, then filter the resulting alias in the outer query.
10. 🔥 Live Debugging Challenge
SELECT employee_name, ROW_NUMBER() FROM employees;
OVER() clause. Fix: ROW_NUMBER() OVER (ORDER BY salary DESC).Goal: Top earner per department. Formula: PARTITION BY employee_id
PARTITION BY department.11. ROW_NUMBER vs. RANK vs. DENSE_RANK
Observe how identical scores (₹72,000 tie) are treated across the 3 ranking functions:
| Employee | Salary ₹ | ROW_NUMBER() | RANK() | DENSE_RANK() |
|---|---|---|---|---|
| Rahul | 72,000 | 1 | 1 | 1 |
| Arjun | 72,000 | 2 (Distinct) | 1 (Shared tie) | 1 (Shared tie) |
| Priya | 65,000 | 3 | 3 (Skips #2) | 2 (Gap-free) |
12. 🔥 Real Business Case (Customer Order History)
Extract the most recent order for each customer:
| Customer ID | Order Date | Order ID | Amount ₹ |
|---|---|---|---|
| CUST-10 | 2026-08-10 | #5003 | ₹15,400 |
| CUST-20 | 2026-07-05 | #5005 | ₹9,800 |
| CUST-30 | 2026-06-18 | #5006 | ₹24,000 |
13. 🔥 Final Challenge (Regional Top 2 Sales)
Retrieve the top 2 sales transactions for every region with deterministic sale_id tie-breaking:
| Region | Rank | Salesperson | Sale ID | Amount ₹ |
|---|---|---|---|---|
| North | #1 | Karan | 901 | ₹85,000 |
| North | #2 | Sunil | 902 | ₹85,000 |
| North | #3 | Pooja | 903 | ₹42,000 |
| South | #1 | Deepak | 904 | ₹95,000 |
| South | #2 | Anjali | 905 | ₹91,000 |
| South | #3 | Vijay | 906 | ₹64,000 |
| West | #1 | Sanjay | 908 | ₹82,000 |
| West | #2 | Meera | 907 | ₹78,000 |
14. Quick Check & Knowledge Assessment
Knowledge Assessment: SQL ROW_NUMBER() Function
Test your mastery of sequence generation, PARTITION BY, CTE filtering, and deduplication patterns.
1. What does the SQL ROW_NUMBER() window function return?
15. Accuracy & Official Specification
- Dialect Support: ANSI SQL:2003, PostgreSQL 8.4+, MySQL 8.0+, SQLite 3.25+, SQL Server 2005+, Oracle 8i+.
- Determinism: If the ordering columns have duplicate values, the order of row number assignment is arbitrary unless additional tie-breaker columns are provided in
ORDER BY.