select
H1.CLASS_NAME as CLASS
,H1.PERSON_NAME as PERSON
,H1.AMT as HOUSEHOLDS
from
HOUSEHOLD as H1
where
H1.PERSON_CODE <> '1'
and H1.CLASS_CODE <> '01'
and H1.AMT = (
select
MAX(H2.AMT)
from
HOUSEHOLD as H2
where
H2.CLASS_CODE = H1.CLASS_CODE
and H2.PERSON_CODE <> '1'
and H2.CLASS_CODE <> '01'
group by
H2.CLASS_CODE
)
order by
H1.CLASS_CODE asc;