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

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

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.