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.

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.

Ebook Download
View all
Learn
View all