select
CLASS
,PERSON
,HOUSEHOLDS
from
(

select 
a.CLASS_NAME AS CLASS
,a.PERSON_NAME AS PERSON
,a.AMT AS HOUSEHOLDS
,rank()OVER(PARTITION BY CLASS_NAME ORDER BY AMT DESC) as ranking
FROM
HOUSEHOLD a
where PERSON_CODE!='1'
and CLASS_CODE!='01'

)b
where ranking=1
order by CLASS