Illustrate the difference between Rank & DenseRank with an example

Illustrate the difference between Rank & DenseRank with an example

Rank and DenseRank can be confusing at times. This articles tries to explain the clear difference between these two functions. To illustrate how these functions are used, assume we want to assign a sequential order, or rank, to people within a department based on salary. We might create a new calculated column and use the RANK function like this: 

Rank([SAL], "asc", [DEPT]) 

Which will show the following:

    DEPT    NAME             SAL       rank
---------- ---------- ---------- ----------
      30   JAMES            950            1
      30   WARD             1250          2
      30   MARTIN          1250          2
      30   TURNER         1500          4
      30   ALLEN            1600          5
      30   BLAKE            2850          6

What we see here is where two people have the same salary they are assigned the same rank. When multiple rows share the same rank the next rank in the sequence is not consecutive.  Notice we are missing rank "3".

The DenseRank() function works like the Rank() function except that it assigns consecutive ranks.  So the following expression: 

DenseRank([SAL], "asc", [DEPT]) 

Would yield these results:

    DEPT    NAME             SAL       rank
---------- ---------- ---------- ----------
      30   JAMES             950           1
      30   WARD             1250          2
      30   MARTIN           1250         2
      30   TURNER          1500         3
      30   ALLEN             1600         4
      30   BLAKE            2850          5