Anyone know how to select the 3rd highest num...

[Simon_Robinson]

Anyone know how to select the 3rd highest number from a list?

[Suvrutt_Gurjar]

Hi @Simon_Robinson,Please try

MIN(TOP((SORT([List Column],TRUE)),3))

[Bellave_Jayaram]

Try Max([Numbers] - {Max([Numbers]),Max([Numbers] - {Max([Numbers])})})

[RezaRaoofi]

Well, I am guessing the numbers are not necessarily unique numbers, are they? I tested different combination of expressions, and finally the only way I could make it work was as follows:

Assuming in Students table [ID] is Key column, and [Score] (which can be duplicate) is the third highest number that we need, creating 3 Virtual Columns with formulas below gave me the correct result:

1. [Sorted] = ORDERBY(Students[ID],[Score],true) 2) [Distinct] = SELECT([Sorted][Score],true,true) 3) [ThirdHighest] =MIN(TOP([Distinct],3))

If the scores cannot be duplicate you can drop virtual column #2, (i.e. [Distinct]).

[Suvrutt_Gurjar]

Hi @RezaRaoofi That is really

a thorough thought process (that numbers may not be unique)

and your solution is also equally detailed. Kudos !

[RezaRaoofi]

@Suvrutt_Gurjar thanks!

[Gil_Littman_App]

You could also use the new INDEX() function to get the nth item of a sorted list. help.appsheet.com - INDEX() INDEX() help.appsheet.com

[RezaRaoofi]

@Gil_Littman_AppSheet Wow! When did INDEX() come out?!

That would simplify and replace steps #3 with something like this:

[ThirdHighest] = INDEX([Distinct],3)

We used to get an update on new features/functions here in community; I hope that good old tradition is still in place.

[Gil_Littman_App]

@RezaRaoofi About 2 weeks ago. I guess we didn’t do a very good job communicating it.

[RezaRaoofi]

I see.

Yea, new functions have a history of being missed out in feature updates and documentation, but I see the recent improvement in functions documentation format which looks nice!