Results 1 to 10 of 10
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Grouping inside sub-queary

    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 15:56.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote 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. #3
    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 15:57.

  4. #4
    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. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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 18:00.

  10. #10
    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
  •