I have an issue with google sheets, and I'm a bit new to the query functions so I'm not positive on how to correct this issue. Assume I have a list of data with arcade game high-scores. So the data will look something like this, but much longer and with multiple games:
Player Name | Arcade Game | Date | Score |
---|---|---|---|
Player A | Pac Man | 1/5/20 | 1000 |
Player B | Pac Man | 4/6/20 | 800 |
Player C | Pac Man | 3/15/20 | 900 |
Player A | Pac Man | 12/10/20 | 1100 |
Now I'm trying to write a query that will pull by game, the top 5 high scores. However, I want to exclude duplicates player entries from the title. So if player A is at the arcade every day, and consistently gets high scores they could occupy all 5 of the top scores. However, I want my query request to only include them once, so their top score is included, but then the other players are listed, even though player A technically has multiple higher scores.
This means instead of my query returning "Player A, Player A, Player A" into each row, it returns "Player A, Player C, Player B"
Currently my query (with no duplicate removal) looks like this:
=QUERY('Data'!$A:$D, "Select A,D,C where "B='Pac Man' Order by D Desc Limit 5")
This returns a list of the top 5 high scores for the selected game, while also listing the date and player. However, I am unsure how to edit my query to remove all scores from each player except their top score.
I have tried using the "group by" query language, but was unsuccessful and only ended up with errors. I could also take the original data, and run it through a second function to clean up the duplicates, and then run my query on the updated data, although that seems like a lot of effort for something query should be able to do.
Thanks!
Try
=QUERY(Data!A:D, "Select A, max(D), max(C) where B='Pac Man' Group by A Order by max(D) Desc Limit 5 label max(D) 'Date', max(C) 'Score'", 1)
and see if that helps?