with tmp as (
select
    class_code
    ,class_name as "CLASS"
    ,person_name as "PERSON"
    ,amt as "HOUSEHOLDS"
    ,row_number() over(partition by class_name order by amt desc) "順位"
from
    household
where
    person_code <> '1'
    and class_code <> '01'

)
select
    class
    ,person
    ,households
from
    tmp
where
    "順位" = 1
order by
    class_code asc
;