SELECT
CLASS,
PERSON_NAME AS PERSON,
HOUSEHOLDS
FROM
(SELECT
CLASS_NAME AS CLASS,
PERSON_CODE AS P_CODE,
MAX(AMT) AS HOUSEHOLDS
FROM
HOUSEHOLD
WHERE
PERSON_CODE != '1'
AND
CLASS_CODE !='01'
GROUP BY CLASS_NAME) t1
LEFT JOIN
HOUSEHOLD t2
ON
P_CODE = PERSON_CODE
AND
CLASS = CLASS_NAME
GROUP BY CLASS_NAME
ORDER BY CLASS_CODE