question

BI DWH BALA avatar image
BI DWH BALA asked

Question on Rank Analytic function?

I am using a anlaytic function called Rank() to display rank of salaries.

select deptno, ename, sal, rank() over (partition by deptno order by sal desc) r from emp order by deptno, sal desc

This is working fine. But here Rank is not giving continuous ranks.

I mean Let's say the data as follows.

DEPTNO ENAME             SAL      R


    10 KING             5000      1
    20 CLARK            2450      2
    30 MILLER           1300      3

    20 SCOTT            3000      1
    20 FORD             3000      1
    20 JONES            2975      3
    20 ADAMS            1100      4
    20 SMITH             800      5

    30 BLAKE            2850      1
    30 ALLEN            1600      2
    30 TURNER           1500      3
    30 MARTIN           1250      4
    30 WARD             1250      4
    30 JAMES             950      6

here if you see, if the 2 persons salaries are same the same rank is being assigned. This is fine. But for the next persion rank is being skipped to bigger rank.

In the above example for dept 20, the employee JONES rank is 3. I want to show it as 2.

How can we do this? Is there any possibility ?

Thank you, Regards

BI DWH BALA

oraclerankanalytic-functions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Christian13467 avatar image
Christian13467 answered

The function RANK is defined to give a "sport" ranking. If there are more than one on the same rank the following ranks are skipped. RANK in documentation.

What you are looking for is DENSE_RANK. It produces a dense rank.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot Christian13467. This is what exactly I am looking for. Its showing consecutive ranks. Thank you so much!
0 Likes 0 ·
HillbillyToad avatar image
HillbillyToad answered

But it's not really 2.

It's like at a PGA golf event, if there's a tie for 2nd place, there is no 3rd place.

B/c there are more than 2 people in front of you, there are 3 people in front of you. That's why it jumps.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thats what I was thought. But it seems there is another funciton DENSE_RANK which solves my rerquired scenario.
0 Likes 0 ·
Tariq Rahiman avatar image
Tariq Rahiman answered

Where two people have the same salary they are assigned the same rank. When multiple rows share the same rank the next rank in the sequence is not consecutive.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yep. But I am looking for conseqcutive rank. Christian gave a function named DENSE_RANK which solved my scenario.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.