select
     CLASS
    ,PERSON
    ,HOUSEHOLDS
from
(
select
     main.CLASS_NAME as CLASS
    ,main.CLASS_CODE as CLASS_CODE
    ,main.PERSON_NAME as PERSON
    ,main.AMT as HOUSEHOLDS
    ,row_number() over(partition by main.CLASS_NAME order by main.AMT desc) as rank
from
    HOUSEHOLD as main
where
    main.PERSON_CODE <> '1' and main.CLASS_CODE <> '01'
)
where
    rank = 1
order by
     CLASS_CODE asc