Lookup 2nd and 3rd highest values

[RayMR]

I have a games table that records different games using a uniqueid for the different games.  Then I have a players table that also uses an uniqueid for each player.  I have a 3rd table called gamestats.  This is a child of the Games table where I select the players and enter a score for them.  

In the Games Table - This expression is getting the top score for each game in a VC - MAXROW("GameStats", "Score", ([GameID] = [_THISROW].[GameID]))

How do get the 2nd and 3rd highest score

for each game?

The pic below shows the GamesStats table

TIA,

Ray

 

[Suvrutt_Gurjar]

If understanding of your requirement is correct, please try below.

There should be a column in the Games table named something like [Related GameStatss]

Please add another VC called say [First_Three_Scores] in the Games table with an expression something like

SELECT( [Related GameStatss][StatsID], [Score], TRUE)

The above expression will give all the records for a game with descending scores order.

For the 3 records with top 3 scores for each game , please try in [First_Three_Scores]

TOP(SELECT( [Related GameStatss][StatsID], [Score], TRUE), 3)

[RayMR]

This is result

[Suvrutt_Gurjar]

Sorry my bad, I mistyped after testing at my end in a similar set up.

Please try

TOP(ORDERBY( [Related GameStats][StatsID], [Score], TRUE), 3)

[RayMR]

Thank you!  That worked perfect.  Is there a way to extract just one value at a time?  A VC for 2nd place another for 3rd place?

[Suvrutt_Gurjar · Accepted solution]

For first place , please try

LIST(INDEX(ORDERBY( [Related GameStats][StatsID], [Score], TRUE), 1))

Second place 

LIST(INDEX(ORDERBY( [Related GameStats][StatsID], [Score], TRUE), 2))

Third place

LIST(INDEX(ORDERBY( [Related GameStats][StatsID], [Score], TRUE), 3))

[RayMR]

Thank you!