Window Functions in SQL: ROW_NUMBER(), RANK(), and DENSE_RANK() Explained

---

Table of Contents

1. Introduction
2. What Are Window Functions?
3. Why Use Window Functions?
4. Basic Syntax of a Window Function
5. Introduction to ROW_NUMBER()
6. Example: Using ROW_NUMBER()
7. Introduction to RANK()
8. Example: Using RANK()
9. Introduction to DENSE_RANK()
10. Example: Using DENSE_RANK()
11. Key Differences Between the Three
12. Using PARTITION BY with Window Functions
13. Using ORDER BY in Window Functions
14. Real-World Use Case: Top N Records per Group
15. Pagination with ROW_NUMBER()
16. Filtering First Row in Each Partition
17. Performance Tips for Window Functions
18. Common Errors and Debugging
19. Best Practices for Window Function Usage
20. Summary and What’s Next

---

1. Introduction

Window functions are a powerful feature in SQL that allow calculations across a set of table rows related to the current row, without collapsing the result set. Unlike aggregates, they maintain row-level granularity.

---

2. What Are Window Functions?

A window function performs a calculation across a set of rows (called a “window”) related to the current row. The syntax always includes the OVER() clause, which defines the window.

---

3. Why Use Window Functions?

• Add ranking, running totals, moving averages
• Get row numbers per partition
• Compare current row with previous/next
• Avoid subqueries for common reporting patterns

---

4. Basic Syntax of a Window Function

sqlCopyEditfunction_name() OVER (
  PARTITION BY column1
  ORDER BY column2
)

You can use it in the SELECT clause like any other expression.

---

5. Introduction to ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a partition, ordered by a specified column.

---

6. Example: Using ROW_NUMBER()

sqlCopyEditSELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

This gives each employee a unique rank within their department, sorted by salary.

---

7. Introduction to RANK()

RANK() assigns a ranking within a partition, but ties get the same rank, and it skips numbers after ties.

---

8. Example: Using RANK()

sqlCopyEditSELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

If two employees have the same salary, they get the same rank, and the next rank is skipped.

---

9. Introduction to DENSE_RANK()

DENSE_RANK() is similar to RANK() but doesn’t skip ranks after ties.

---

10. Example: Using DENSE_RANK()

sqlCopyEditSELECT name, department, salary,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

If two employees tie, they share the same rank, and the next rank increments by one, not skipping any numbers.

---

11. Key Differences Between the Three

Function	Unique?	Ties?	Gaps in Rank?
ROW_NUMBER()	Yes	No ties	No
RANK()	No	Ties share	Yes
DENSE_RANK()	No	Ties share	No

---

12. Using PARTITION BY with Window Functions

Partitioning divides rows into subsets:

sqlCopyEdit... OVER (PARTITION BY department ORDER BY salary DESC)

Calculates rankings within each department.

---

13. Using ORDER BY in Window Functions

Determines the sequence of numbering:

sqlCopyEdit... OVER (ORDER BY order_date)

Without ORDER BY, row numbering becomes arbitrary and unpredictable.

---

14. Real-World Use Case: Top N Records per Group

sqlCopyEditWITH ranked_sales AS (
  SELECT employee_id, region, amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rk
  FROM sales
)
SELECT * FROM ranked_sales WHERE rk <= 3;

Returns top 3 sales per region.

---

15. Pagination with ROW_NUMBER()

sqlCopyEditWITH paginated AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
  FROM products
)
SELECT * FROM paginated WHERE row_num BETWEEN 11 AND 20;

Perfect for implementing offset-based pagination.

---

16. Filtering First Row in Each Partition

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

Returns highest-paid employee from each department.

---

17. Performance Tips for Window Functions

• Use indexes on ORDER BY columns
• Limit result sets when possible
• Avoid using window functions in filters directly — wrap in CTEs
• Window functions are faster than correlated subqueries in most cases

---

18. Common Errors and Debugging

Error	Cause
Window function without OVER()	Syntax error
Using window function in WHERE	Not allowed — use CTE or subquery instead
Unexpected results	Check PARTITION BY and ORDER BY logic

---

19. Best Practices for Window Function Usage

• Always use PARTITION BY if ranking within a group
• Use meaningful aliases (row_num, rank, etc.)
• Test RANK() vs DENSE_RANK() for ties
• Wrap window logic in a CTE for reusability and clarity
• Never assume row order without explicit ORDER BY

---

20. Summary and What’s Next

Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() empower SQL developers to write advanced analytics queries without subqueries. They’re indispensable for ranking, pagination, and grouped calculations.