DROP TABLE IF EXISTS character_achievement_points_tmp;

CREATE TABLE `character_achievement_points_tmp` (
  `guid` int(11) unsigned NOT NULL,
  `achievement` int(11) unsigned NOT NULL,
  `points` int(11) NOT NULL default '0',
  PRIMARY KEY  (`guid`,`achievement`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `character_achievement_points_tmp` (`guid`, `achievement`) SELECT DISTINCT `guid`, `achievement` FROM `character_achievement`;

UPDATE character_achievement_points_tmp SET points = (SELECT rewardPoints FROM dbc_achievement WHERE dbc_achievement.Id = character_achievement_points_tmp.achievement);

DROP TABLE IF EXISTS character_achievement_points;

CREATE TABLE `character_achievement_points` (
  `guid` int(11) unsigned NOT NULL,
  `name` varchar(12) NOT NULL default '',
  `achievement_points` int(11) NOT NULL default '0',
  PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `character_achievement_points` (`guid`, `name`) SELECT DISTINCT `guid`, `name` FROM `characters`;

UPDATE character_achievement_points SET achievement_points = (SELECT SUM(points) FROM character_achievement_points_tmp WHERE character_achievement_points.guid = character_achievement_points_tmp.guid);

DROP TABLE IF EXISTS character_achievement_points_tmp;

SELECT guid, name, achievement_points FROM character_achievement_points ORDER BY achievement_points DESC LIMIT 10;