1. Registered User
Join Date
May 2009
Posts
5

Hi,

I have a problem which is driving me nuts and I just can't see how to get the answer. I would really appreciate it if somebody could even point me in the right direction.

I have a simple db that store details of a game. A game consists of rounds and the winner has the highest total of all rounds. I am trying to write a query that adds up the round scores for each player for each game and returns the highest score and player id of each game. It sounds so easy but I'm struggling to do the grouping inside a subqueary to get only the max score for each game.

Here is a simplified version of the table

Code:
```+----+------+--------+-------+-------+
| id | game | player | round | score |
+----+------+--------+-------+-------+
|  1 |    1 |      1 |     1 |     3 |
|  2 |    1 |      2 |     1 |     3 |
|  3 |    1 |      1 |     2 |     1 |
|  4 |    1 |      2 |     2 |     2 |
|  5 |    1 |      3 |     1 |     5 |
|  6 |    1 |      3 |     2 |     1 |
|  7 |    2 |      1 |     1 |     8 |
|  8 |    2 |      2 |     1 |     4 |
|  9 |    2 |      3 |     1 |     1 |
| 10 |    2 |      1 |     2 |     1 |
| 11 |    2 |      2 |     2 |     4 |
| 12 |    2 |      3 |     2 |     6 |
+----+------+--------+-------+-------+```
If I run
select game, player, sum(score) from scorecard group by game, player
I get
Code:
```+------+--------+------------+
| game | player | sum(score) |
+------+--------+------------+
|    1 |      1 |          4 |
|    1 |      2 |          5 |
|    1 |      3 |          6 |
|    2 |      1 |          9 |
|    2 |      2 |          8 |
|    2 |      3 |          7 |
+------+--------+------------+```
And now all I need to do is limit the result to the higest scores in each game which I think I need to do by using a sub-query and max but I always get the wrong answer.

Can anybody point out what I'm missing or how I could re-structure the table to make this work.

Many thanks if you can help at all.

Robin
Last edited by railes; 05-08-09 at 14:56.

2. vaguely human
Join Date
Jun 2007
Location
London
Posts
2,527
Originally Posted by railes
And now all I need to do is limit the result to the higest scores in each game which I think I need to do by using a sub-query and max but I always get the wrong answer.

Can anybody point out what I'm missing or how I could re-structure the table to make this work.
Start by using order by to get the top scores first. Then use limit to just show a certain number of them. There's no need for sub-queries.

Mike

3. Registered User
Join Date
May 2009
Posts
5
Hi Mike,

Thanks for the suggestion. I have tried messing about with limit and order but order could give me two scores from the same game. In the test data I supplied I have simplified the table a lot to try and make it easier to understand and I included that possibility.

If I try
select game, player, sum(score) from scorecard group by game, player order by sum(score) desc limit 2
I get
Code:
```+------+--------+------------+
| game | player | sum(score) |
+------+--------+------------+
|    2 |      1 |          9 |
|    2 |      2 |          8 |
+------+--------+------------+```
Had you another idea on how to do the order or limit?

Robin
Last edited by railes; 05-08-09 at 14:57.

4. vaguely human
Join Date
Jun 2007
Location
London
Posts
2,527
Two scores from the same game? I don't understand sorry - can you give a better example. If it's including two games with the same score then that's a different matter.

5. vaguely human
Join Date
Jun 2007
Location
London
Posts
2,527
You will need a sub query then. Use the same code but check that a record doesn't exists for the same game with a higher score.

You can make your data (much) easier to read by highlighting your tables and then using the # button to preserve the spacing etc in your data.

The SQL is more difficult than you imagined because the table design isn't optimal (I believe).

Mike

6. Registered User
Join Date
May 2009
Posts
5
Thanks Mike for top tip on # for tables. I've redone them to help others understand more clearly.

On optimal table design, I have been trying to think of a different way of designing it to make it easier to get the results but everything I've tried so far has just given me repitition of date. Any thoughts?

7. vaguely human
Join Date
Jun 2007
Location
London
Posts
2,527
Few questions before I commit myself. What's the game you're playing? It might help just to picture what's going on. Are there always 2 players in a game. Do people ever get by's? Is it always a knockout tournament or do you need to cover round robin or swiss style tournaments. I'm hoping to be playing in a round robin backgammon tournament next week!

8. Registered User
Join Date
May 2009
Posts
5
It's a version of the card game whist. 4 players, it is normally played over 5 or 10 rounds (one for each suit plus 'no trumps') and on each round a player may score between 0 and 23 points. Winner is highest combined score.

We play with friends reguarly and we like to record the numbers. We probably have about 50+ games recorded and I thought it would be fun and easy to enter them into at database and pull out stats like who had won most games, who came 2nd most often, most successful on each suit etc. Thinking about it now, I could record the finishing positions (ie 1 to 4) against a game id in a seperate table but since it can be derived from the numbers it seems like wasted effort.

I'm sure the answer is a query of the query above, but it's just out of my reach.

9. vaguely human
Join Date
Jun 2007
Location
London
Posts
2,527
It might be out of my reach too but this is my 1st guess :

Code:
```select	t1.game, t1.player, t1.tot_score
from	(       -- total score for each player for each game
select	game, player, sum( score ) tot_score
from	scorecard
group by game, player
) t1,
(       -- highest score in each game
select	game, max( tot_score ) max_score
from	(       -- total score for each player for each game
select	game, player, sum( score ) tot_score
from	scorecard
group by game, player
) t2
group by game
) t3
where	t1.game = t3.game
and t1.tot_score = t3.max_score```
I haven't tried the code so there's probably a few little errors in there. If 2 players have the same total score (is this possible?) then both names are shown.
Last edited by mike_bike_kite; 05-08-09 at 17:00.

10. Registered User
Join Date
May 2009
Posts
5
It's a very good first guess. That last group statement is fantastic and the bit that I keep missing. I'll plug it into some proper data and see what it looks like. I'll let you know. Very clever and very quick.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•