SQL rank function explanation with example.

Introduction

Rank functions are a powerful feature in SQL that allow us to assign ranks to rows based on certain criteria. In this article, we will delve into the world of SQL rank functions using a practical example involving student scores. We'll explore how different rank functions behave, particularly when faced with ties and gain a comprehensive understanding of their usage.

Problem Scenario

Consider a hypothetical scenario where we have a table called "Students" with columns for student ID, name, and score. We want to assign ranks to the students based on their scores, but what happens when we encounter ties, where multiple students have the same score?

Let's understand this with an example:

The dataset:

Now in the above student table, we need to rank those students based on their scores. In SQL we can do it in three different ways as mentioned below:

1. RANK()
2. DENSE_RANK()
3. ROW_NUMBER()

A detailed explanation of these three functions with an example is given below.

1. Ranking with RANK()

The RANK() function assigns a unique rank to each row based on the specified ordering criteria. In case of ties, it will skip the subsequent ranks. Let's see how it works in our example:

Consider the above dataset of students table for all upcoming examples.

SELECT
    ID,
    Name,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS rank
FROM
    Students;        

The result will be:

As you can see, Emma and John both have a score of 92, resulting in a tie. The RANK() function assigns a rank of 1 to both of them and skips the subsequent rank, resulting in John having a rank of 3 instead of 2.

2. Ranking with DENSE_RANK()

The DENSE_RANK() function also assigns ranks based on the specified ordering criteria, but it does not skip subsequent ranks in case of ties. Let's modify our query to use DENSE_RANK():

SELECT
    ID,
    Name,
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS dense_rank
FROM
    Students;        

The result will be:

In this case, both Emma and John still have a dense rank of 1, but subsequent ranks are not skipped. Liam and Ethan receive ranks of 3 and 4, respectively, following the tied ranks.

3. Ranking with ROW_NUMBER()

The ROW_NUMBER() function assigns a unique number to each row, irrespective of ties. Let's modify our query once again to use ROW_NUMBER():

SELECT
    ID,
    Name,
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS row_number
FROM
    Students;        

The result will be:

With ROW_NUMBER(), each row receives a unique number, regardless of ties. Emma and John both receive distinct row numbers, even though they have the same score.

Conclusion

Rank functions in SQL are invaluable when it comes to assigning ranks to rows based on specific criteria. Understanding how these functions behave, particularly in scenarios involving ties, is crucial. By using the RANK(), DENSE_RANK(), and ROW_NUMBER() functions, we can handle ties differently to suit our requirements.

In this article, we explored a practical example involving student scores to grasp the concepts of rank functions. We observed how the RANK() function skips subsequent ranks, DENSE_RANK() retains subsequent ranks, and ROW_NUMBER() assigns unique numbers to each row.

With this knowledge, you can confidently utilize rank functions to analyze and rank data in various business scenarios, such as leaderboard rankings, competitive analysis, and more.

Remember, SQL rank functions may have slight variations depending on the database system you are using. It's always a good practice to consult the documentation of your specific database for precise syntax and behaviour.

Happy ranking!