# What is the difference between Row_Number(), Rank() and Dense_Rank() in SQL?

Standard

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:

1. 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.
2. 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”
3. 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

Paras Doshi