RANK() vs DENSE_RANK() in SQL: What’s the Difference?

Many RDBMSs include both a rank() and a dense_rank() function in their list of window function offerings. At first glance, these functions might appear to do the same thing. However, there’s one important difference between these functions, and you will definitely need to be aware of this difference when choosing which function to use.

Definitions

The difference between rank() and dense_rank() can be seen in their definitions. Here’s how the MySQL documentation defines each of these functions:

`rank()`	Returns the rank of the current row within its partition, with gaps. Peers are considered ties and receive the same rank. This function does not assign consecutive ranks to peer groups if groups of size greater than one exist; the result is noncontiguous rank numbers.
`dense_rank()`	Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. This function assigns consecutive ranks to peer groups; the result is that groups of size greater than one do not produce noncontiguous rank numbers.

So the main difference is that rank() includes gaps if there are ties, whereas dense_rank() doesn’t.

Example

Here’s an example to demonstrate the difference between these two functions:

SELECT 
    productname,
    productprice,
    dense_rank() OVER ( ORDER BY productprice ),
    rank() OVER ( ORDER BY productprice )
FROM products;

Result:

           productname           | productprice | dense_rank | rank 
---------------------------------+--------------+------------+------
 Bottomless Coffee Mugs (4 Pack) |         9.99 |          1 |    1
 Hammock                         |        10.00 |          2 |    2
 Long Weight (green)             |        11.99 |          3 |    3
 Tea Pot                         |        12.45 |          4 |    4
 Long Weight (blue)              |        14.75 |          5 |    5
 Left handed screwdriver         |        25.99 |          6 |    6
 Right handed screwdriver        |        25.99 |          6 |    6
 Sledge Hammer                   |        33.49 |          7 |    8
 Straw Dog Box                   |        55.99 |          8 |    9
 Chainsaw                        |       245.00 |          9 |   10
(10 rows)

We can see that both the left handed screwdriver and right handed screwdriver share the same rank (because they’re both the same price).

In the dense_rank column, we can see that there’s no gap between that rank (6) and the next rank (7). In other words, the values are contiguous. In the rank column however, there is a gap. Specifically, it skips 7 and goes straight to 8.

So if you’re going to add a ranking column to a query, you’ll definitely want to make sure you use the most appropriate function for your needs.

Other Ranking Functions

Depending on your DBMS, there may be other ranking functions available to you. For example you may be able to use a percent_rank() function or a cume_dist() function.

Here’s an example of adding these to the above query:

SELECT 
    productname,
    productprice AS price,
    dense_rank() OVER win AS dr,
    rank() OVER win AS r,
    cume_dist() OVER win,
    percent_rank() OVER win
FROM products
WINDOW win AS ( ORDER BY productprice );

Result:

           productname           | price  | dr | r  | cume_dist |    percent_rank    
---------------------------------+--------+----+----+-----------+--------------------
 Bottomless Coffee Mugs (4 Pack) |   9.99 |  1 |  1 |       0.1 |                  0
 Hammock                         |  10.00 |  2 |  2 |       0.2 | 0.1111111111111111
 Long Weight (green)             |  11.99 |  3 |  3 |       0.3 | 0.2222222222222222
 Tea Pot                         |  12.45 |  4 |  4 |       0.4 | 0.3333333333333333
 Long Weight (blue)              |  14.75 |  5 |  5 |       0.5 | 0.4444444444444444
 Left handed screwdriver         |  25.99 |  6 |  6 |       0.7 | 0.5555555555555556
 Right handed screwdriver        |  25.99 |  6 |  6 |       0.7 | 0.5555555555555556
 Sledge Hammer                   |  33.49 |  7 |  8 |       0.8 | 0.7777777777777778
 Straw Dog Box                   |  55.99 |  8 |  9 |       0.9 | 0.8888888888888888
 Chainsaw                        | 245.00 |  9 | 10 |         1 |                  1
(10 rows)

In this example I moved the ORDER BY clauses to a named window so that I wouldn’t have to replicate them. I defined the named window with the WINDOW clause at the end of the query, then referred to that window in each OVER clause.