Difference between Rank() and Dense_Rank() function in SQL Server

We use either Rank() or Dense_Rank() to get consecutive numbering. But there is a difference between these two functions.

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.