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
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
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
So, we find that Rank() skips the ranking number when it gets same OrdCount
but Dense_Rank() maintains ranking order.
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 |
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 |
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 |
No comments:
Post a Comment