#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    15
    Rep Power
    0

    Join to "grouped by" subset


    Supposing I have a table of players and a table of scores. Players may play multiple games one or more times. For example:

    Code:
    drop table if exists players; 
    create temporary table  players (id int, name varchar(10));
    insert into players
        select 1, 'Tommy' union all
        select 2, 'Billy' union all
        select 3, 'Johnny' union all
        select 4, 'Freddie'; 
    drop table if exists results;
    create table results (playerID int, game varchar(10), score int);
    insert into results
        select 1, 'Game1', 3 union all
        select 1, 'Game1', 4 union all
        select 1, 'Game2', 1 union all
        select 2, 'Game2', 3 union all
        select 2, 'Game2', 1 union all
        select 3, 'Game1', 2 union all
        select 3, 'Game2', 1 union all
        select 4, 'Game1', 1 union all
        select 4, 'Game1', 2;
    Now, I want to select only players who played both Game1 and Game2, and I want their highest score in each game, i.e.:

    PlayerID Name Game1 Game2
    1 Tommy 4 1
    3 Johnny 2 1

    What's the best way to do that?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    Code:
    select players.id,
           players.name,
           max(game1.score) as game1,
           max(game2.score) as game2
      from players
      join results as game1
        on players.id = game1.playerid
       and game1.game = 'game1'
      join results as game2
        on players.id = game2.playerid
       and game2.game = 'game2'
     group
        by id,
           name
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,375
    Rep Power
    391
    Code:
    select players.id,
           players.name,
           max(game1.score) as game1,
           max(game2.score) as game2
      from players
      join results as game1
        on players.id = game1.playerid
       and game1.game = 'game1'
      join results as game2
        on players.id = game2.playerid
       and game2.game = 'game2'
     group
        by players.id,
           players.name
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    15
    Rep Power
    0
    Ahhhh... I was quite close, but kept trying to do the max() and/or group by in the join some how (and failing, naturally!).

    Thanks very much.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Don't forget that a PRIMARY KEY is a prerequisite of all tables in SQL.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    15
    Rep Power
    0
    Originally Posted by cafelatte
    Don't forget that a PRIMARY KEY is a prerequisite of all tables in SQL.
    That was just an example to describe the problem as clearly as I could. The actual tables are quite different (nothing to do with games anyway) and they do have PKs.

    That said, although I understand that PKs are strongly recommended, are they really needed in all circumstances?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    You need some way of uniquely identifying each row. This is the PRIMARY KEY. This can be a natural key or a surrogate key. When using a surrogate, it's good practice to have a UNIQUE elsewhere within the table.

    Strictly speaking, it's not always necessary. Your example illustrates that fact. But what if you wanted to find out whether Player 1 achieved her highest score on her first attempt or her second? You have no way of doing that because at present there is no way of uniquely identifying each row.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    15
    Rep Power
    0
    Understood - thanks.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    15
    Rep Power
    0
    OK, I now want to look at all results (rather than max. scores) but only from those individuals that played both games. So the tables again (with a PK now added to the results table) are:

    Code:
    create temporary table  players (id int, name varchar(10));
    insert into players
        select 1, 'Tommy' union all
        select 2, 'Billy' union all
        select 3, 'Johnny' union all
        select 4, 'Freddie'; 
    create table results (id int, playerID int, game varchar(10), score int);
    insert into results
        select 1, 1, 'Game1', 3 union all
        select 2, 1, 'Game1', 4 union all
        select 3, 1, 'Game2', 1 union all
        select 4, 2, 'Game2', 3 union all
        select 5, 2, 'Game2', 1 union all
        select 6, 3, 'Game1', 2 union all
        select 7, 3, 'Game2', 1 union all
        select 8, 4, 'Game1', 1 union all
        select 9, 4, 'Game1', 2;
    ... and the output should now be something like:

    ID PlayerID Name Game Score
    1 1 Tommy Game1 3
    2 1 Tommy Game1 4
    3 1 Tommy Game2 1
    6 3 Johnny Game1 2
    7 3 Johnny Game2 1

    Sorry to have to ask, I'm sure I should be able to work it out myself but I often seem to have something of a mental block when it comes to SQL, I'm afrraid...

IMN logo majestic logo threadwatch logo seochat tools logo