Illustrate the difference between Rank & DenseRank with an example
Article ID: KB0081885
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All supported versions |
Description
Rank and DenseRank can be confusing at times. This articles tries to explain the clear difference between these two functions.
Issue/Introduction
This articles explains the difference between Rank and DenseRank with an example.
Resolution
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
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
Feedback
Yes
No
Powered by
