The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

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()

The difference is easy to remember. For the examples, let’s assume we have this table (using PostgreSQL syntax):

1 2 3 4 5

CREATE TABLE t(v) AS SELECT * FROM (   VALUES('a'),('a'),('a'),('b'),         ('c'),('c'),('d'),('e') ) t(v)

ROW_NUMBER()

… assigns unique numbers to each row within the PARTITION given theORDER 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 BYclause 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 |

(see also this SQLFiddle)

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 |

(see also this SQLFiddle)

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 |

(see also this SQLFiddle)

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 |

(see also this SQLFiddle)

In fact, ROW_NUMBER() prevents you from using DISTINCT, becauseROW_NUMBER() generates unique values across the partition before DISTINCTis 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 |

(see also this SQLFiddle)

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 |

(see also this SQLFiddle)

Note that unfortunately, the WINDOW clause is not supported in all databases.

SQL is awesome

These things can be written very easily using SQL window functions. Once you get a hang of the syntax, you won’t want to miss this killer feature in your every day SQL statements any more. Excited?