Finding the Longest Winning Streak

[K5]

I am trying to extract the longest winning streak for each player in an app that records game results. However, I am stuck and cannot figure out how to do it.

Currently, I have two tables:

Game Records Table:

• GameNo (consecutive)
• 1st Place
• 2nd Place
• 3rd Place
• 4th Place

Players Table:

• PlayerID
• PlayerName

The ranking columns (1st to 4th) in the Game Records table reference the Players table.

I want to record the longest winning streak for each player and display the player name with the longest winning streak record. However, I am not sure how to do it.

I thought of first finding the No. of the most recent winning record for the player in question. Then, I would find the No. that is smaller than that No. and where a player other than the player in question won. The difference between these two numbers would be the recent winning streak. I could then repeat this process until No.1 to extract all the winning streaks and display the largest one.

However, I am stuck on how to implement the iterative process.

Any advice would be greatly appreciated.

[AleksiAlkio]

Do you want to do this with the "1st Place" column only?

[K5]

Yes, I would like to do this only in the "1st Place" column.

[AleksiAlkio · Accepted solution]

Add one additional column to your Games table. That would read the winner from 1st Place column but add a number to the end. Something like a5g2fh4k_1. If the winner is different than in the game just before, use 1, if it's the same,  +1 and it would be a5g2fh4k_2 and so on. This can be a normal column.

In the Players table, add a virtual column where it finds all those rows where the player's ID matches, sort them with this new column, and pick the biggest value. Then you have the max number for each players.

[K5]

Sorry for the delay in replying, and Thank you very much.
I understood the logic you mentioned, but I was having a hard time understanding how to get the value of the previous column in order to implement it. I thought it would be OK if I use LIST and something as like expressions just -1 the ID column to , but it didn't work. In the case of to use ROWNUMBER is OK, but I was stuck because it would be blank when I deleted a row.
Finally, I decided to re-count GemeNO by MAX([GemeNO]) +1 with the columns as real columns (default value 1), and the rest was done generally as you taught me!
Thank you very much for your kind assistance!

[AleksiAlkio]

Good to hear!