What are Row Number, Partition, Rank, and Dense Rank?

Introduction

In SQL, ranking functions are essential when dealing with ordered datasets. They allow users to assign unique or grouped rankings based on specific conditions. Four key ranking functions used in SQL are ROW_NUMBER(), RANK(), DENSE_RANK(), and PARTITION BY.

These functions help in scenarios like:

• Paginating results
• Identifying duplicates
• Ranking sales or scores
• Creating sequential row numbers

In this article, we will explore these functions in detail with practical SQL examples.

---

1. Understanding ROW_NUMBER()

The ROW_NUMBER() function assigns a unique row number to each record based on the order specified in the ORDER BY clause. It does not consider duplicate values and assigns a sequential number to each row.

Syntax:

sqlCopyEditSELECT column_name, 
       ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) AS row_num
FROM table_name;

Example:

Let’s say we have a Sales table:

SaleID	Customer	Amount
1	John	500
2	Alex	300
3	John	200
4	Alex	400
5	John	100

Now, applying ROW_NUMBER() with PARTITION BY Customer:

sqlCopyEditSELECT SaleID, Customer, Amount,
       ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Amount DESC) AS row_num
FROM Sales;

Output:

SaleID	Customer	Amount	Row_Num
1	John	500	1
3	John	200	2
5	John	100	3
4	Alex	400	1
2	Alex	300	2

Key Takeaways:

• ROW_NUMBER() always provides a unique ranking.
• If used without PARTITION BY, it generates a continuous sequence for all rows.
• It is useful for pagination and removing duplicates.

---

2. Understanding RANK()

The RANK() function assigns a rank to each row within a partition. If there are duplicate values, they receive the same rank, and the next rank is skipped.

Syntax:

sqlCopyEditSELECT column_name, 
       RANK() OVER (PARTITION BY column_name ORDER BY column_name) AS rank
FROM table_name;

Example:

Using the same Sales table, let’s apply RANK():

sqlCopyEditSELECT SaleID, Customer, Amount,
       RANK() OVER (PARTITION BY Customer ORDER BY Amount DESC) AS rank
FROM Sales;

Output:

SaleID	Customer	Amount	Rank
1	John	500	1
3	John	200	2
5	John	3
4	Alex	400	1
2	Alex	300	2

Key Takeaways:

• Duplicate values get the same rank.
• The ranking skips numbers when duplicates occur.
• Used when ranking competitions (e.g., sports tournaments).

---

3. Understanding DENSE_RANK()

The DENSE_RANK() function works like RANK(), but it does not skip ranking numbers when duplicates exist.

Syntax:

sqlCopyEditSELECT column_name, 
       DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name) AS dense_rank
FROM table_name;

Example:

Applying DENSE_RANK() to the Sales table:

sqlCopyEditSELECT SaleID, Customer, Amount,
       DENSE_RANK() OVER (PARTITION BY Customer ORDER BY Amount DESC) AS dense_rank
FROM Sales;

Output:

SaleID	Customer	Amount	Dense_Rank
1	John	500	1
3	John	200	2
5	John	3
4	Alex	400	1
2	Alex	300	2

Key Takeaways:

• Duplicate values get the same rank.
• Unlike RANK(), it does not skip ranks.
• Used for salary slabs, leaderboard rankings, etc.

---

4. Understanding PARTITION BY

The PARTITION BY clause is used within window functions to divide the result set into partitions and apply functions like ROW_NUMBER(), RANK(), or DENSE_RANK() to each partition separately.

Example:

sqlCopyEditSELECT SaleID, Customer, Amount,
       ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Amount DESC) AS row_num,
       RANK() OVER (PARTITION BY Customer ORDER BY Amount DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY Customer ORDER BY Amount DESC) AS dense_rank
FROM Sales;

Output:

SaleID	Customer	Amount	Row_Num	Rank	Dense_Rank
1	John	500	1	1	1
3	John	200	2	2	2
5	John	100	3	3	3
4	Alex	400	1	1	1
2	Alex	300	2	2	2

---

Conclusion

Function	Duplicate Handling	Rank Skipping	Use Case
ROW_NUMBER()	No	Yes	Pagination, Removing Duplicates
RANK()	Yes	Yes	Competition Rankings
DENSE_RANK()	Yes	No	Salary Slabs, Leaderboards

Understanding these SQL functions will help you handle ordered data effectively. Whether you need unique row numbers, ranking with skips, or continuous ranking, these functions provide a structured approach.