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 |
