In what situations would Rank and Dense Rank be useful?

Question

I know the syntax for using the Rank and Dense Rank functions but I can't find any uses in the real world for this .

For example DENSE_RANK

ranking       userid
1               500
1               500
2               502
2               502

and Rank

Ranking UserID
    1   500
    1   500
    1   500
    1   500
    1   500
    1   500
    1   500
    8   502
    8   502
    8   502
    8   502
    8   502
    8   502
    8   502
    15  504

I can't understand how the 1,1 2,2 values would be useful in the real world. On the other hand, I do understand very clearly what the real-world uses for row_number over partition are; I just can't find what can I do with this kind of information (dense & regular rank)

marc_s : you right i will remove , it was in my head since ive accomplished these functions without using the ready ones in 2008. — Royi Namir, Jul 26 '11 at 14:16

Kevin Burton · Accepted Answer · 2011-07-26T18:41:13.767

4

you could use it to find the top n rows for each group

There is a very good explanation here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2920665938600

edited Jul 26 '11 at 18:41

answered Jul 26 '11 at 14:01

Kevin Burton

11,676
2
24
37

score 3 · Answer 2 · answered Jul 26 '11 at 14:07

3

Specific examples would be arbitrary and not necessarily helpful. So long as you understand what they do (q.v. @Kevin Burton's link), and can remember at least vaguely that this functionality exists, then if or when a situation comes up where they would be useful--if not critical--you'll be able to pull them out of the database developer's bag of tricks. (I've used RANK once, maybe twice, and it was very useful each time, but I can't--and don't need to--recall the details without looking them up.)

answered Jul 26 '11 at 14:07

Philip Kelley

39,426
11
57
92

1

That applies to pretty much every one of those OLAP features, such as `CUBE`, `ROLLUP`, `CONNECT BY`, etc... – Lukas Eder Jul 26 '11 at 14:13
Yep--and it just feels great to drag out some ridiculous obscure function to convert 30 lines of query and subquery into 6 of a single query. – Philip Kelley Jul 26 '11 at 14:27
Yes, and be the only one that can still decipher its meaning - or extend it :) – Lukas Eder Jul 26 '11 at 14:35
1

Nah, obscure functions are well documented in BOL. Obscure code and/or logic, now, that's where your job security is. :) – Philip Kelley Jul 26 '11 at 16:01

In what situations would Rank and Dense Rank be useful?

2 Answers2