/*世帯人員コードごとのマックスを求めるが、
それに値する世帯階層コードは何かを出せばいい

1.世帯人員コードごとのMAX
2.世帯人員コード、所得階層コードごとのMAX
3.JOIN*/

SELECT HO2.CLASS_NAME AS CLASS
,HO1.PERSON_NAME AS PERSON
,HO2.AMT AS HOUSEHOLDS

FROM
(SELECT CLASS_CODE AS CLASS_CODE
        ,CLASS_NAME AS CLASS_NAME
        ,MAX(AMT) AS AMT
        
    FROM HOUSEHOLD
   WHERE PERSON_CODE<>'1'
          AND CLASS_CODE <>'01'
    GROUP BY CLASS_CODE,CLASS_NAME ) AS HO2
LEFT JOIN
     (SELECT 
             PERSON_NAME AS PERSON_NAME
             ,CLASS_NAME AS CLASS_NAME
             ,CLASS_CODE AS CLASS_CODE
             ,MAX(AMT)AS AMT
        FROM HOUSEHOLD
        WHERE PERSON_CODE<>'1'
          AND CLASS_CODE <>'01'
        GROUP  BY  PERSON_CODE
             ,PERSON_NAME 
             ,CLASS_NAME 
             ,CLASS_CODE  ) AS HO1    
ON HO1.CLASS_CODE=HO2.CLASS_CODE
AND HO1.AMT=HO2.AMT
ORDER BY HO2.CLASS_CODE