Need MYSQL query for finding lowest score per game player

Question

I have a game on Facebook called Rails Across Europe. I have a Best Scores page where I show the players with the best 20 scores, which in game terms refers to the lowest winning turn. The problem is that there are a small number of players who play frequently, and their scores dominate the page. I'd like to make the scores page open to more players. So I thought that I could display the single lowest winning turn for each player instead of displaying all of the lowest winning turns for all players. The problem is that the query for this eludes me. So I hope that one of you brilliant StackOverflow folks can help me with this.

I have included the relevant MYSQL table schemas below. Here are the the table relationships:

• player_stats contains statistics for either a game in progress or a completed game. If a game is in progress, winning_turn is zero (which means that games with a winning_turn of zero should not be included in the query). player_stats has a game_player table id reference.

• game_player contains data describing games currently in progress. game_player has a player table id reference.

• player contains data describing a person who plays the game.

Here's the query I'm currently using:

  'SELECT p.fb_user_id, ps.winning_turn, gp.difficulty_level, c.name as city_name, g.name as goods_name, d.cost
   FROM game_player as gp, player as p, player_stats as ps, demand as d, city as c, goods as g
   WHERE p.status = "ACTIVE" AND gp.player_id = p.id AND ps.game_player_id = gp.id
   AND d.id = ps.highest_demand_id AND c.id = d.city_id AND g.id = d.goods_id
   AND ps.winning_turn > 0
   ORDER BY ps.winning_turn ASC, d.cost DESC LIMIT '.$limit.';';

Here are the relevant table schemas:

--
-- Table structure for table `player_stats`
--

CREATE TABLE IF NOT EXISTS `player_stats` (
  `id` int(11) NOT NULL auto_increment,
  `game_player_id` int(11) NOT NULL,
  `winning_turn` int(11) NOT NULL,
  `highest_demand_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `game_player_id` (`game_player_id`,`highest_demand_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3814 ;

--
-- Table structure for table `game_player`
--

CREATE TABLE IF NOT EXISTS `game_player` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `game_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  `player_number` int(11) NOT NULL,
  `funds` int(10) unsigned NOT NULL,
  `turn` int(10) unsigned NOT NULL,
  `difficulty_level` enum('STANDARD','ADVANCED','MASTER','ULTIMATE') NOT NULL,
  `date_last_used` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `game_id` (`game_id`,`player_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3814 ;

--
-- Table structure for table `player`
--

CREATE TABLE IF NOT EXISTS `player` (
  `id` int(11) NOT NULL auto_increment,
  `fb_user_id` char(255) NOT NULL,
  `fb_proxied_email` text NOT NULL,
  `first_name` char(255) NOT NULL,
  `last_name` char(255) NOT NULL,
  `birthdate` date NOT NULL,
  `date_registered` datetime NOT NULL,
  `date_last_logged_in` datetime NOT NULL,
  `status` enum('ACTIVE','SUSPENDED','CLOSED') NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fb_user_id` (`fb_user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1646 ;

Answer 1

I don't have time to really dig into your table defs, but I'm doing the very same thing in my game, so I'll just give you my query:

(I'm actually getting ID of the played games that should be displayed in the highscore list)

SELECT matchId
FROM (
    SELECT accountId,
           MIN(finalScore) as bestScore,
           scoringType
    FROM PlayedMatches
    GROUP BY accountId
) AS x INNER JOIN PlayedMatches AS f
ON f.accountId = x.accountId AND
   f.finalScore = x.bestScore
ORDER BY finalScore DESC
LIMIT 20

You can read more on this type of queries on

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Answer 2

It might be worth throwing the DISTINCT operator in before the p.fb_user_id in your selection to see what happens. That might just do the trick. (Disclaimer: I've never used the DISTINCT operator in this fashion and am not certain that it will work.)