One of the most obvious and useful set of window functions are ranking functions where rows from your result set are ranked according to a certain scheme. There are three ranking functions:

`ROW_NUMBER()`

`RANK()`

`DENSE_RANK()`

**ROW_NUMBER()**

… assigns unique numbers to each row within the `PARTITION`

given the `ORDER BY`

clause. So you’d get:

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:

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:

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:

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.

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:

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 |