If the database that you work with supports Window/Analytic functions then the chances are that you have run into SQL use-cases where you have wondered about the difference between Row_Number(), Rank() and Dense_Rank(). In this post, I’ll show you the difference:
So, let’s just run all of them together and see what the output looks like.
Here’s my query: (Thanks StackExchange!)
select DisplayName,Reputation, Row_Number() OVER (Order by Reputation desc) as RowNumber, Rank() OVER (Order by Reputation desc) as Rank, Dense_Rank() OVER (Order by Reputation desc) as DenseRank from users
Which gives the following output:
DisplayName Reputation RowNumber Rank DenseRank
-------------------- ---------- --------- ---- ---------
Hardik Mishra 9999 1 1 1
Alex 9997 2 2 2
Omnipresent 9997 3 2 2
Sergei Basharov 9993 4 4 3
Oleg Pavliv 9991 5 5 4
Jason Creighton 9991 6 5 4
Aniko 9991 7 5 4
Notlikethat 9990 8 8 5
ZeMoon 9989 9 9 6
Carl 9987 10 10 7
...
...
... Note that all the functions are essentially are “ranking” your rows but there are subtle differences:
- Row_Number() doesn’t care if the two values are same and it just ranks them differently. Note row #2 and #3, they both have value 9997 but they were assigned 2 and 3 respectively.
- Rank() — Now unlike Row_Number(), Rank() would consider that the two values are same and “Rank” them with same value. Note Row #2 and #3, they both have value 9997 and so both were assigned Rank “2” — BUT notice the Rank “3” is missing! In other words, it introduces some “gaps”
- Dense_Rank() — Now Dense_Rank() is like Rank() but it doesn’t leave any gaps! Notice that the Rank “3” in the DenseRank field.
I hope this clarified the differences between these SQL Ranking functions — let me know your thoughts in the comments section