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

    Join Date
    Mar 2005
    Posts
    418
    Rep Power
    20

    Joining one table with another based on MAX value in column.


    Hi,
    I have a table called "game" and a table called "move" similar to this: game{ game_id, game_name } and move{ move_id, game, the_move }. Where game_id and move_id are auto increment values and the 'game' field in the move table is a foreign-key for the game-table.

    Now i want to select all games and join to that the last move that was made in that game (thus has the highest move_id for that game). Example game table:
    Code:
    1, "game1name"
    2, "game2name"
    3, "game3name"
    Example movetable:
    Code:
    1, 1, walkleft
    2, 1, jump
    3, 2, jump
    4, 2, walkright
    Then i want to join the tables in such a way that the result is like this:
    Code:
    1, "game1name","jump"
    2, "game2name","walkright"
    Because jump is the last move for game1 and "walkright" is the last move for game2.

    So far i got this:
    Code:
    SELECT 
    	game.game_id,
    	game.game_name,
    	move.the_move
    FROM game as game 
    LEFT JOIN move as move 
    ON game.game_id=move.game
    But this selects all games with all move combinations I think and I dont know how to narrow the selection such that all games are returned once with the last move data included. Note this is just an example, im applying this to something totally different but the problem is the same.

    Hope you can help me out.
    Thanks!
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    In this case I suggest that you create a Derived Table that contains the gameid and the highest moveid.
    And then you join with that derived table to get the rest of the data. So something like this should solve your problem:
    Code:
    SELECT
      g.game_id
      ,g.game_name
      ,mt.the_move
    FROM (
      SELECT
        game
        ,MAX(move_id) AS MaxMoveID
      FROM
        movetable
      GROUP
        BY
          game
    ) AS MaxMT
    INNER
      JOIN
        game g
        ON game.game_id = MaxMT.game
    INNER
      JOIN
        movetable mt
        ON mt.id = MaxMT.id
    /Stefan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    418
    Rep Power
    20
    Thanks! That seems to be in the direction of what im looking for.

    However, there is one problem. The derived table has only the games for which there is a move. Some games may not have any moves yet, but I do want those games to be shown in my results, with the move fields having no value.
    Code:
    (SELECT game, MAX(move_id) AS MaxMoveID
     FROM movetable
     GROUP BY game) AS MaxMT
    That will only contain the games for which there is a move.

    Do you know any other way, or maybe how to adjust the derived table such that it always has each game regardless of whether there exists a move for it?

    The real code I tried for my project:
    Code:
    SELECT 
    	g.game_id,
    	g.player1,
    	g.player2,
    	g.creation_time,
    	g.is_finished,
    	g.white_player,
    	g.last_move_timestamp,
    	g.winner,
    	g.cause_of_game_finish,
    	g.player1_wants_draw,
    	g.player2_wants_draw,
    	g.resigned,
    	m.move_number,
    	m.origin_x,
    	m.origin_y,
    	m.destination_x,
    	m.destination_y,
    	m.promotion_piece,
    	m.offer_draw,
    	m.timestamp 
    FROM (SELECT game, MAX(move_id) AS max_move_id FROM move GROUP BY game) AS max_move 
    INNER JOIN 	game g 
    ON 			g.game_id=max_move.game 
    INNER JOIN 	move m 
    ON 			m.move_id=max_move.max_move_id
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    in this case you shoud start from game table and left outer join the rest of the tables

    Code:
    SELECT
      g.game_id
      ,g.game_name
      ,mt.the_move
    FROM game g
     left outer join (
      SELECT
        game
        ,MAX(move_id) AS MaxMoveID
      FROM
        movetable
      GROUP
        BY
          game
    ) AS MaxMT
        ON game.game_id = MaxMT.game
    left outer JOIN
        movetable mt
        ON mt.id = MaxMT.id
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by Stefan1
    Do you know any other way
    there are actually about a dozen different ways

    check groupwise max

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo