select
class_name as 'CLASS',
person_name as 'PERSON',
amt as 'HOUSEHOLDS'
from (
select
class_code,
class_name,
person_name,
amt,
rank() over(partition by class_name order by amt desc) as rank
from household
where
person_code != '1'
and
class_code != '01'
)
where rank = 1
order by class_code