How to get the rank number?

[#1]

Hi All,

i have a table shown like below:

Name	Score
A	60
B	55
C	60
D	60
E	70

What can I write in the script to get the rank number of each row? What's more, for the rank number of same score, I need to get the mean rank number for those. The table below is what I want to get after:

Name	Score	Rank
A	60	3
B	55	1
C	60	3
D	60	3
E	70	5

Just like the 'Proc rank' function in SAS....

Thanks so much!

[eduardo_sommer]

Hi,

Use the following expression for the rank: =rank(total -Score,2,1)

Don't forget the minus sign in front of the Score field, since your rankin starts with the minimum value.

Eduardo

[#3]

Hi,

Thanks so much for your help and i have tired that.But I would like to get this rank in script not in the table. Is there any code could be used in the script to get the rank?

Thanks!

[eduardo_sommer]

Hi,

I created a script that calculates the AveragedRank as you required:

Table:

load * inline [

Name, Score

A, 60

B, 55

C, 60

D, 60

E, 70

]

;

left join (Table)

load Score,

     count(Name) as Names

resident Table

group by Score;

RankedTable:

load Name,

     Score,

     if(Score <> peek(Score), rangesum(Peek(CalculatedRank),ceil(Names/2)), Peek(AveragedRank)) as AveragedRank,

     if(isnull(peek(CalculatedRank)), 1, peek(CalculatedRank)+1) as CalculatedRank

Resident Table

order by Score;   

Drop Table Table;

Here is the straight table, comparing the rank function, as I proposed yesterday and tha AveragedRank calculated in the script.

Tell me if this helped you.

Eduardo

[simenkg]

I suppose you can do something like this.

left join(Fact)

Load Name,

          sum(Score) as %RankKey

resident Fact

Group By Name;

left join(Fact)

Load Distinct %RankKey,

    RowNo() as Rank

order by %RankKey asc;

[simenkg]

This test script did the job. Perhaps you can find some use for it:

Test1:

load * inline [

Name, Score

A, 60

B, 55

C, 60

D, 60

E, 70

];

left join(Test1)

load

Name,

Sum(Score) as %Rank_Key

resident Test1

group by Name;

Test2:

NoConcatenate

load Distinct %Rank_Key

resident Test1;

left join(Test1)

load %Rank_Key, RowNo() as Rank

resident Test2

order by %Rank_Key asc;

Drop table Test2;