try

select class, guns, displacement from classes join ( 
select max( guns) as guns , displacement from classes group by displacenet ) maxes
on classes.guns = maxes.guns and classes.displacement = maxes.displacement