Select rows with highest version for a shared key

Question

Here's the Schema in mysql:

CREATE  TABLE IF NOT EXISTS `labs_test`.`games` (
  `game_id` INT NOT NULL AUTO_INCREMENT ,
  `key` VARCHAR(45) NOT NULL ,
  `config` BLOB NOT NULL ,
  `game_version` BIGINT NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`game_id`) ,
  INDEX `key` (`key`(8) ASC) ,
  INDEX `version` (`game_version` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

I've tried using MAX(game_version) to no avail. Should I give up the dream and use a sub query?

Answer 1

Use:

SELECT g.*
  FROM GAMES g
  JOIN (SELECT t.key,
               MAX(t.game_version) AS max_version
          FROM GAMES t
      GROUP BY t.key) x ON x.key = g.key
                       AND x.max_version = g.game_version

Should I give up the dream and use a sub query?

Some would call what I used in my answer a subquery, but it'd be more accurate to call it a derived table/inline view. Unless I'm missing something, I don't see how you can get the records without some form of self-join (joining the same table onto itself). Here's the EXISTS alternative:

SELECT g.*
  FROM GAMES g
 WHERE EXISTS(SELECT NULL
                FROM GAMES t
               WHERE t.key = g.key
            GROUP BY t.key
              HAVING MAX(t.game_version) = g.game_verion

Use the EXPLAIN plan to determine which performs best, rather than be concerned with whether or not to use a subquery.