select
    PF_CODE as "都道府県コード",
    PF_NAME as "都道府県名",
    max(iif (rank = 1, NATION_NAME, 0)) as "1位 国名",
    max(iif (rank = 1, AMT, 0)) as "1位 人数",
    max(iif (rank = 2, NATION_NAME, 0)) as "2位 国名",
    max(iif (rank = 2, AMT, 0)) as "2位 人数",
    max(iif (rank = 3, NATION_NAME, 0)) as " 3位 国名",
    max(iif (rank = 3, AMT, 0)) as " 3位 人数",
    sum(coalesce(AMT, 0)) as "合計人数"
from
    (
        select
            *,
            rank() over (
                partition by
                    FOREIGNER.PF_CODE
                order by
                    FOREIGNER.AMT desc,
                    NATIONALITY.NATION_CODE asc
            ) as rank
        from
            PREFECTURE
            join FOREIGNER on FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
            and FOREIGNER.NATION_CODE <> '113'
            join NATIONALITY on NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
    )
group by
    PF_CODE
order by
    "合計人数" desc,
    PF_CODE asc;