snoyes

SELECT 
	d1.* 
FROM
	(
		SELECT 
			playerid, 
			weaponid, 
			SUM(kills) AS kills 
		FROM 
			player_stats_total
		GROUP BY playerid, weaponid
	) AS d1
	LEFT JOIN (
		SELECT 
			playerid, 
			weaponid, 
			SUM(kills) AS kills 
		FROM 
			player_stats_total
		GROUP BY playerid, weaponid
	) AS d2 ON d1.weaponid = d2.weaponid AND d1.kills < d2.kills
WHERE
	d2.playerid IS NULL