SQL Window Functions | ROW_NUMBER, RANK & DENSE_RANK

Window Functions

Videos Discussion

In many SQL queries, we need more than just filtering or grouping data.

Often, we want to analyze data row by row while still keeping the original rows.

Consider scenarios like:

Finding the top 3 highest-paid employees in each department

Ranking students based on marks

Assigning serial numbers to ordered results

Identifying duplicate records

Using GROUP BY alone does not help here because it collapses rows. This is where Window Functions become extremely powerful.

What Are Window Functions?

Window functions perform calculations across a set of related rows, called a window, without merging rows.

In simple words:

Window functions let you analyze data while keeping every row visible.

They are widely used in:

Reporting

Analytics

Rankings

Pagination

Trend analysis

Syntax

function_name() OVER (
    PARTITION BY column_name
    ORDER BY column_name
)

PARTITION BY - divides data into groups (optional)

ORDER BY - defines the order inside the window

OVER - tells SQL this is a window function

ROW_NUMBER()

Purpose: Assign a Unique Number to Each Row

ROW_NUMBER() assigns a unique sequential number to each row based on the specified order.

Key Characteristics

Always produces unique numbers

No gaps or duplicates

Useful for pagination and identifying duplicates

Example: Row Number by Salary

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

Even if two employees have the same salary, they get different row numbers.

RANK()

Purpose: Rank Rows with Gaps

RANK() assigns the same rank to rows with equal values but leaves gaps in ranking.

Key Characteristics

Same values - same rank

Skips numbers after ties

Useful in competition-style ranking

Example: Salary Ranking

SELECT emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_value
FROM employees;

If two employees share rank 1, the next rank becomes 3.

DENSE_RANK()

Purpose: Rank Rows Without Gaps

DENSE_RANK() is similar to RANK(), but it does not skip numbers after ties.

Key Characteristics

Same values - same rank

No gaps in ranking

Ideal for leaderboard systems

Example: Dense Salary Ranking

SELECT emp_name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_value
FROM employees;

If two employees are rank 1, the next rank is 2.

Using PARTITION BY with Ranking Functions

Ranking can also be done within groups, such as departments.

Example: Ranking Employees per Department

SELECT emp_name, department_id, salary,
       RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS dept_rank
FROM employees;

Each department gets its own ranking sequence.

Difference Between ROW_NUMBER, RANK and DENSE_RANK

Feature	ROW_NUMBER	RANK	DENSE_RANK
Unique numbers	Yes	No	No
Handles ties	No	Yes	Yes
Gaps in ranking	No	Yes	No
Best use case	Pagination	Competition ranking	Leaderboards

Real World Use Cases

Window functions are commonly used in:

Top-N queries

Leaderboards

Salary analysis

Removing duplicates

Report generation

They are essential for modern SQL analytics.

Performance Notes

Window functions are optimized in modern databases

Indexing on ORDER BY columns improves performance

Avoid unnecessary partitions in large datasets

Two Minute Drill

Window functions analyze rows without grouping
ROW_NUMBER gives unique numbers
RANK allows gaps
DENSE_RANK avoids gaps
PARTITION BY creates group wise ranking
Essential for analytics queries