SQL Window Function – Difference between Rank() Dense_Rank() and Row_Number()

Rank():

• Use Case: When you want to assign a unique rank to each distinct row based on the values in a specific column, and you want to skip the next rank in case of tied values.
• Example: Ranking students based on their scores in a test where if two students have the same score, the next rank will be skipped.

Dense_Rank()

• Use Case: Similar to Rank, but without skipping ranks for tied values. Dense Rank is useful when you want to assign a unique rank to each distinct row based on the values in a specific column without any gaps in the ranking.
• Example: Ranking athletes in a competition based on their finishing times, where if two athletes have the same time, they both receive the same rank without any gap.

Row_Number()

• Use Case: When you want to assign a unique, sequential number to each row in the result set, irrespective of the values in a specific column. Row Number is often used for creating a unique identifier for each row.
• Example: Assigning a serial number to orders in a sales dataset, where the order of appearance is more important than the values in any specific column.

Showing Difference between Rank, Dense Rank , and Row Number in a single view:

Partition By Clause:

• Use Case: When you want to generate rankings or row numbers within specific groups or partitions of the data. The PARTITION BY clause allows you to restart the ranking or numbering for each unique value in a particular column.
• Example: Ranking employees within each department based on their salaries, where the ranking is reset for each department.

Mastering Rank, Dense Rank, and Row Number in SQL enhances your ability to unravel insights within datasets. These functions provide a dynamic toolkit for data analysts and developers, allowing them to navigate and interpret data with finesse. Whether you seek distinct rankings, continuous sequences, or unique identifiers, understanding when and how to deploy these functions is key to unlocking their full potential in SQL.

I also have posted on LinkedIn the difference between Rank() and Dense_Rank() in a simpler way as following:

References

https://youtu.be/Ww71knvhQ-s?si=JWt2islHd3oQFfbn