WITH c AS (
    SELECT
        rank() over(partition by f.pf_code order by f.amt desc, f.nation_code asc) as rownum, 
        f.PF_CODE,
        f.AMT,
        n.nation_name,
        p.PF_NAME
    FROM FOREIGNER f
    INNER JOIN PREFECTURE p
    ON p.pf_code = f.pf_code
    INNER JOIN NATIONALITY n
    ON n.NATION_CODE = f.NATION_CODE
    WHERE f.NATION_CODE <> '113'
)
SELECT
    a.PF_CODE AS 都道府県コード,
    a.PF_NAME AS 都道府県名,
    c1.NATION_NAME AS "1位 国名",
    c1.AMT AS "1位 人数",
    c2.NATION_NAME AS "2位 国名",
    c2.AMT AS "2位 人数",
    c3.NATION_NAME AS "3位 国名",
    c3.AMT AS "3位 人数",
    a.total AS 合計人数
FROM (
    SELECT
    ct.pf_code,
    ct.pf_name,
    sum(ct.amt) AS total
    FROM c ct
    GROUP BY ct.PF_CODE, ct.pf_name
) a
INNER JOIN c c1
ON c1.PF_CODE = a.PF_CODE
AND c1.rownum = 1
INNER JOIN c c2
ON c2.PF_CODE = a.PF_CODE
AND c2.rownum = 2
INNER JOIN c c3
ON c3.PF_CODE = a.PF_CODE
AND c3.rownum = 3
ORDER BY 合計人数 DESC, a.PF_CODE ASC