Understanding SQL Ranking Functions: RANK, DENSE_RANK, and ROW_NUMBER

In SQL, ranking functions are used to assign a rank or a sequential number to rows in a result set based on the values of one or more columns. These functions are particularly useful for tasks like sorting, ranking, and partitioning data. The most commonly used ranking functions are RANK, DENSE_RANK, and ROW_NUMBER. This article explains these functions and demonstrates how to use them with SQL queries.

Key Concepts

1. RANK()

   • Description: Assigns a rank to each row within a result set based on the specified ordering. Rows with the same values receive the same rank, and subsequent ranks are adjusted to account for ties, resulting in gaps in the ranking sequence.
   • Example: If two rows share the highest rank (1), the next rank assigned will be 3, leaving a gap.

2. DENSE_RANK()

   • Description: Similar to RANK, but without gaps in the ranking values. Rows with the same values receive the same rank, and the next distinct value gets the immediate next rank.
   • Example: If two rows share the highest rank (1), the next rank assigned will be 2, without any gaps.

3. ROW_NUMBER()

   • Description: Assigns a unique sequential integer to each row based on the specified ordering. It does not consider the values of the rows for ranking and does not create ties or gaps.
   • Example: Each row is given a unique number starting from 1, regardless of the values in the rows.

Practical Example

Consider a table named StudentMarks with the following data:

Name	Marks
A	100
B	100
C	89

To apply these ranking functions, we need to order the data by the Marks column in descending order.

SQL Queries

Here’s how you can use RANK, DENSE_RANK, and ROW_NUMBER functions in SQL to get the ranking of each row:

1. DENSE_RANK

SELECT
    Name,
    Marks,
    DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRank
FROM
    StudentMarks;

Output:
| Name | Marks | DenseRank |
|------|-------|-----------|
| A | 100 | 1 |
| B | 100 | 1 |
| C | 89 | 2 |

• Explanation: Both A and B have the highest marks (100) and share rank 1. The next distinct mark (89) gets rank 2.

2. RANK

SELECT
    Name,
    Marks,
    RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM
    StudentMarks;

Output:
| Name | Marks | Rank |
|------|-------|------|
| A | 100 | 1 |
| B | 100 | 1 |
| C | 89 | 3 |

• Explanation: Both A and B share rank 1. The next distinct mark (89) receives rank 3, creating a gap because ranks 1 and 2 are taken.

3. ROW_NUMBER

SELECT
    Name,
    Marks,
    ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNumber
FROM
    StudentMarks;

Output:
| Name | Marks | RowNumber |
|------|-------|-----------|
| A | 100 | 1 |
| B | 100 | 2 |
| C | 89 | 3 |

• Explanation: Each row is assigned a unique sequential number based on the descending order of marks. There are no ties or gaps.

Conclusion

SQL ranking functions such as RANK, DENSE_RANK, and ROW_NUMBER are powerful tools for organizing and analyzing data. Understanding the differences between these functions allows you to choose the appropriate ranking method based on your needs, whether it’s handling ties, avoiding gaps, or simply numbering rows sequentially. By using these functions effectively, you can gain valuable insights and present data in a meaningful way.