We use either Rank() or Dense_Rank() to get consecutive numbering. But there
is a difference between these two functions.
By using an example, we will see the difference. We have following resultset
Prod_Id |
OrdCount |
10 |
199 |
18 |
188 |
28 |
188 |
32 |
171 |
14 |
264 |
12 |
163 |
75 |
253 |
84 |
347 |
34 |
412 |
65 |
209 |
If we use RANK() and the query is
SELECT Prod_Id, OrdCount,
RANK() OVER (ORDER BY SUM(OrdCount) DESC) AS Ranks
FROM OrderDetail ORDER BY Ranks
the result will be
Prod_Id |
OrdCount |
Ranks |
34 |
412 |
1 |
84 |
347 |
2 |
14 |
264 |
3 |
75 |
253 |
4 |
65 |
209 |
5 |
10 |
199 |
6 |
18 |
188 |
7 |
28 |
188 |
7 |
32 |
171 |
9 |
12 |
163 |
10 |
But if we use DENSE_RANK() and the query is
SELECT Prod_Id, SUM(OrdCount) AS OrdCount,
DENSE_RANK() OVER (ORDER BY SUM(OrdCount) DESC) AS Ranks
FROM OrderDetail ORDER BY Ranks
the result will be
Prod_Id |
OrdCount |
Ranks |
34 |
412 |
1 |
84 |
347 |
2 |
14 |
264 |
3 |
75 |
253 |
4 |
65 |
209 |
5 |
10 |
199 |
6 |
18 |
188 |
7 |
28 |
188 |
7 |
32 |
171 |
8 |
12 |
163 |
9 |
So, we find that Rank() skips the ranking number when it gets same OrdCount
but Dense_Rank() maintains ranking order.