Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK() in SQL Server?

Solution:

-----------

ROW_NUMBER()
… assigns unique numbers to each row within the PARTITION given the ORDER BY clause. So you’d get:

1 2	SELECT v, ROW_NUMBER() OVER() FROM t

Note that some SQL dialects (e.g. SQL Server) require an explicit ORDER BY clause in the OVER() clause:

1 2	SELECT v, ROW_NUMBER() OVER(ORDER BY v) FROM t

The above query returns:

| V | ROW_NUMBER |
|---|------------|
| a |          1 |
| a |          2 |
| a |          3 |
| b |          4 |
| c |          5 |
| c |          6 |
| d |          7 |
| e |          8 |

RANK()
… behaves like ROW_NUMBER(), except that “equal” rows are ranked the same. If we substitute RANK() into our previous query:

1 2	SELECT v, RANK() OVER(ORDER BY v) FROM t

… then the result we’re getting is this:

| V | RANK |
|---|------|
| a |    1 |
| a |    1 |
| a |    1 |
| b |    4 |
| c |    5 |
| c |    5 |
| d |    7 |
| e |    8 |

As you can see, much like in a sports ranking, we have gaps between the different ranks. We can avoid those gaps by using
DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

1 2	SELECT v, DENSE_RANK() OVER(ORDER BY v) FROM t

… to obtain

| V | DENSE_RANK |
|---|------------|
| a |          1 |
| a |          1 |
| a |          1 |
| b |          2 |
| c |          3 |
| c |          3 |
| d |          4 |
| e |          5 |

One interesting aspect of DENSE_RANK() is the fact that it “behaves like” ROW_NUMBER() when we add the DISTINCT keyword.

1 2	SELECT DISTINCT v, DENSE_RANK() OVER(ORDER BY v) FROM t

… to obtain

| V | DENSE_RANK |
|---|------------|
| a |          1 |
| b |          2 |
| e |          5 |
| d |          4 |
| c |          3 |

In fact, ROW_NUMBER() prevents you from using DISTINCT, because ROW_NUMBER() generates unique values across the partition before DISTINCT is applied:

1 2 3

SELECT DISTINCT v, ROW_NUMBER() OVER(ORDER BY v) FROM t ORDER BY 1, 2

DISTINCT has no effect:

| V | ROW_NUMBER |
|---|------------|
| a |          1 |
| a |          2 |
| a |          3 |
| b |          4 |
| c |          5 |
| c |          6 |
| d |          7 |
| e |          8 |

Putting it all together

A good way to understand the three ranking functions is to see them all in action side-by-side. Run this query

1 2 3 4 5 6 7

SELECT   v,   ROW_NUMBER() OVER(ORDER BY v),   RANK()       OVER(ORDER BY v),   DENSE_RANK() OVER(ORDER BY v) FROM t ORDER BY 1, 2

… or this one (using the SQL standard WINDOW clause, to reuse window specifications):

1 2 3 4 5 6 7

SELECT   v,   ROW_NUMBER() OVER(w),   RANK()       OVER(w),   DENSE_RANK() OVER(w) FROM t WINDOW w AS (ORDER BY v)

… to obtain:

| V | ROW_NUMBER | RANK | DENSE_RANK |
|---|------------|------|------------|
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |