Window Function DENSE_RANK

This article is an extension of the window function, to follow up on each of the window functions.

The DENSE_RANK function will use the ORDER BY expression to determine a rank of a value amongst groups. If a PARTITION BY is present, this is also included in the reset of groups.

The important difference between RANK and DENSE_RANK is that RANK will use to total number of rows to nominate it’s highest rank value; whereas DENSE_RANK will only use the number of ranks calculated, and the highest rank.

Take the following for example:

SELECT sale_id, quantity, unit_cost,
   DENSE_RANK() OVER (ORDER BY quantity DESC),
   RANK() OVER (ORDER BY quantity DESC)
FROM sales
ORDER BY quantity, sale_id;

The sale_id, quantity, and unit_cost are retrieved and ranked by the quantity value. Both RANK and DENSE_RANK are used in this example to illustrate the difference in value distribution (along the number line).

As there is a change in quantity, a new rank value is assigned: