select
a.CLASS_NAME as  CLASS
,a.PERSON_NAME as  PERSON
,ifnull(b.AMT,0) as HOUSEHOLDS
from HOUSEHOLD a
 join 
(
select PERSON_CODE,CLASS_CODE,
ROW_NUMBER() OVER(PARTITION BY CLASS_CODE order by AMT desc) x,amt
from HOUSEHOLD where PERSON_CODE  <> '1' and CLASS_CODE <> '01'
)
b on a.CLASS_CODE=b.CLASS_CODE and a.AMT =b.amt
where a.PERSON_CODE  <> '1' and a.CLASS_CODE <> '01' and b.x =1
 order by a.CLASS_CODE