How to get second Highest Rank for each ID in table

[narband2778]

Hello everyone,

I have Table:

ID Value Rank    

1     10       1

1     20       2

1     30       3

2     25       1

2     35       2

2     42       3

2     45       4

Generally we know, for ID= 1 we will get second highest rank is 2 and for ID=2 second highest rank is 3.

desired Table should be like this:

ID Value Rank

1     20      2

2     42      3

Can someone help me with this?    

[YoussefBelloum]

Hi,

for ID=2 why the second highest rank is 3 ?

[narband2778]

Hi,

For ID = 2 there are 4 records and the highest Rank is 4. So, I want second Highest rank i.e. Rank = 3.

[avinashelite]

could you explain the desired output once ???

For ID 2 its should the value 35 , 2 highest rite

[narband2778]

Hi,

I mean for ID =1 Maximum Rank = 3 but I need second maximum Rank which is 2 for ID=1.

Same for ID = 2 Maximum Rank = 4 but second maximum rank = 3.

i hope this is clear.

[avinashelite · Accepted solution]

Got you try this script

Test:

LOAD * INLINE [

ID,Value,Rank   

1,10,1

1,20,2

1,30,3

2,25,1

2,35,2

2,42,3

2,45,4

];

NoConcatenate

Max_R:

LOAD

ID&'-'& max(Rank)as Key_ID

Resident

Test

Group by

ID

;

Second_MAX:

LOAD

ID&'-'&max(Rank) as Second_Max_Key

//max(Rank) as S_R

Resident

Test

Where not Exists(Key_ID,ID&'-'&Rank)

Group by

ID;

Drop Table Max_R;

NoConcatenate

Result:

LOAD

ID,

Value,

Rank

Resident

Test

Where

Exists(Second_Max_Key,ID&'-'&Rank);

Drop Table Second_MAX,Test;

[narband2778]

Thanks Avinash

[swuehl]

Note that Max() can take an optional second argument to retrieve the n-th highest value.

So you can shorten the script a llttle bit:

Test:
LOAD *, ID & '-'&Rank as Key_ID
INLINE [
ID,Value,Rank 
1,10,1
1,20,2
1,30,3
2,25,1
2,35,2
2,42,3
2,45,4
];



INNER JOIN
LOAD
ID&'-'& max(Rank,2) as Key_ID
Resident
Test
Group by
ID
;

DROP FIELD Key_ID;

[avinashelite]

Yes , but sometime it won't give the accurate result in the script