Solved: Finding the Longest Winning Streak - Page 2

K5 · 03-20-2024 11:42 PM

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

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.

View solution in original post