SQL Highscore Table Database
We have been receiving a couple of questions in regards to games involving scores over the last few weeks. Therefore I put together something I would have wanted to see when I first started out:
If you save the score for each game session you will end up with a database similar to the scores table below.
| player | game | score |
| 1 | A | 150 |
| 2 | A | 250 |
| 1 | B | 250 |
| 2 | B | 50 |
| 3 | B | 125 |
The example above has two game sessions. In the game called A there were two players and in the game called B three players. If we look at player 1, we can see that she lost the first game but won the second.
Usually we’d like to look at all the game data we’ve stored and say how many game sessions a particular player has won. But how do we do that automatically with a database SQL query? It seems simple enough since it’s such a small table, but it can be a little more tricky than you would first expect.
Below is one way to do this. The idea is that we calculate the total number of games the player has participated in and subract the number of games she lost.
SELECT total.number - lost.number FROM (SELECT COUNT(DISTINCT scores.game) AS number FROM scores WHERE scores.player = '1') AS total, (SELECT COUNT(DISTINCT my_scores.game) AS number FROM scores AS my_scores INNER JOIN scores AS other_scores ON my_scores.game = other_scores.game WHERE my_scores.score < other_scores.score AND my_scores.player = '1') AS lost;
So if you’re making a game that saves scores this code might come in handy for you.
Do you see what would happen if two players have the same score?
Do you know how you can improve the performance?
The spring finally hit Umeå, I’m off to enjoy it
Have a great weekend
Peter
-
Brett Unzaga permalink“Do you see what would happen if two players have the same score?” It looks like the players first in the list would be placed above the players that come after him. The first part is fairly straight forward. Get all the games where the player = 1 as a number. The second part was a bit confusing for me, as I have never used ‘inner join’ before. But I think the logic is… for all the games where I have a score, get all the other peoples scores which are also the same game. Check my score vs their score, and if mine is lower, return that game. Then you subtract total – lost. I don’t see any code to handle what to do if one players (total-lost) == another players (total-lost), so it leads me to believe that it will just go in order from first to last. Whoever runs the query first will be at the top, whoever runs the query last will be on the bottom, assuming their numbers are the same… So in the above example. player1 would be first, player2 would be second and player 3 would be third because player1= 2-1, player2 = 2-1, player3 = 1-1
-
Brett Unzaga permalink“Do you know how you can improve the performance?” To me, the biggest drawback is you are using the ’score’ table three times. First you query all the games where the user has a score(as total). Second you query all the games again(as my_scores). Third, you query all the games again(as other_scores). Please keep in mind, I haven’t used inner join before, so I don’t know the details around that command. To me it should go like this: $user_games = all games where user = 1. You now have all the games where the user has a score, no need to query it for the second part. Next part, do $lost = SELECT game FROM $user_games WHERE user.score !=(SELECT MAX(score) from $user_games). So basically, for each game in user_games, get the games where the user.score is not the max. I have to admit, I just looked up ‘mysql get highest value’ in google, so the syntax might not be right, but you get the idea. Query the database once, then use that for the future steps.
