Need MYSQL query for finding lowest score per game player
- by Chris Barnhill
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 ;
