-- with t as (
--     select CLASS_NAME as "CLASS",PERSON_NAME as "PERSON",sum(amt) as "HOUSEHOLDS", class_code, rank() over()
--     from HOUSEHOLD as h
--     where h.CLASS_CODE!='01' and PERSON_CODE!='1'
--     group by 1,2
-- )
-- select CLASS,PERSON, HOUSEHOLDS
with t as (
    select CLASS_NAME as "CLASS", 
    PERSON_NAME as "PERSON",class_code, rank() over(partition by class_name order by amt desc) as r, amt
    from HOUSEHOLD as h
    where h.CLASS_CODE!='01' and PERSON_CODE!='1'
)
select class as "CLASS",
max(case when r=1 then person end) as "PERSON",
max(case when r=1 then amt end) as "HOUSEHOLDS"
from t
group by 1
order by class_code