CODEWITHSUNDEEP
sql-server
Royi Namir
Royi Namir

Reputation: 148744

In what situations would Rank and Dense Rank be useful?

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)

Upvotes: 3

Views: 3123

Answers (2)

Kevin Burton
Kevin Burton

Reputation: 11924

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

Upvotes: 4

Philip Kelley
Philip Kelley

Reputation: 40359

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.)

Upvotes: 3

Related Questions