All Forums Database
kirthi 65 posts Joined 02/12
22 Feb 2016
Do we have a Rank function for this requirement ?

I have a requirement to rank the values, Where the upper & lower bound of the ranks are fixed. 
lowest value is always Rank - 1 and highest value is Rank - 0,  other values in the list needs to distributed between the boundaries evenly.
KEY_COL              VALUE     Rank_value_needed
E                          10344      0
C                          56            0.1

D                          7              0.2

 

F                          7              0.2

 

G                         6               0.4

 

H                         6               0.4

 

B                         2              0.6

 

AA                       2               0.6

 

BB                      2                0.6

 

CC                      2               0.6

 

A                        0                1
 

srivigneshkn 21 posts Joined 02/16
22 Feb 2016

The following query should work for this requirement.
select key_col,val,percent_rank () over (order by val desc) as Rank_value_needed from Table;
 
Output :
Key Col   Val   Rank_Value_Needed
--------    ---    -------------------------

E           10,344 0.000000

C                  56 0.100000

F                    7 0.200000

D                   7 0.200000

H                   6 0.400000

G                   6 0.400000

CC                 2 0.600000

B                   2 0.600000

AA                 2 0.600000

BB                 2 0.600000

A                   0 1.000000

 

Thanks & Regards,

Srivignesh KN

 

You must sign in to leave a comment.