select
    PF_CODE as 都道府県コード,
    PF_NAME as 都道府県名,
    max( case when SUB.RANK = 1 then NATION_NAME ELSE NULL END ) as "1位 国名",
    max( case when SUB.RANK = 2 then AMT ELSE 0 END ) as "1位 人数",
    max( case when SUB.RANK = 1 then NATION_NAME ELSE NULL END ) as "1位 国名",
    max( case when SUB.RANK = 2 then AMT ELSE 0 END ) as "1位 人数",
    max( case when SUB.RANK = 1 then NATION_NAME ELSE NULL END ) as "1位 国名",
    max( case when SUB.RANK = 2 then AMT ELSE 0 END ) as "1位 人数",
    SUM(AMT) as 合計人数
from
    (
        select
            F.PF_CODE as PF_CODE,
            P.PF_NAME as PF_NAME,
            N.NATION_NAME as NATION_NAME,
            F.AMT as AMT,
            rank() over (
                partition by
                    F.PF_CODE
                order by
                    AMT DESC,
                    F.NATION_CODE asc
            ) as RANK
        from
            FOREIGNER as F
            inner join
                NATIONALITY as N
                using(NATION_CODE)
            inner join
                PREFECTURE as P
                using(PF_CODE)
        WHERE
            F.NATION_CODE != '113'
    ) as SUB
group by
    SUB.PF_CODE,
    SUB.PF_NAME
order by
    合計人数 desc,
    都道府県コード asc