with temp1 as(
    select
        PF_CODE, 
        NATION_CODE,
        RANK() OVER(PARTITION BY PF_CODE ORDER BY amt desc, NATION_CODE asc) as rank,
        amt
    from FOREIGNER
    where NATION_CODE != '113'
)
    select
        t.PF_CODE as '都道府県コード', 
        p.PF_NAME as '都道府県名',
        max(
            case when rank = 1 then NATION_NAME else null end 
        ) as '1位 国名', 
        sum(
            case when rank = 1 then amt else 0 end 
        ) as '1位 人数', 
        max(
            case when rank = 2 then NATION_NAME else null end 
        ) as '2位 国名', 
        sum(
            case when rank = 2 then amt else 0 end 
        ) as '2位 人数', 
        max(
            case when rank = 3 then NATION_NAME else null end 
        ) as '3位 国名', 
        sum(
            case when rank = 3 then amt else 0 end 
        ) as '3位 人数',  
        sum(amt) as '合計人数'
    from temp1 as t
        inner join NATIONALITY as n on t.NATION_CODE = n.NATION_CODE
        inner join PREFECTURE as p on t.PF_CODE = p.PF_CODE
    group by t.PF_CODE 
    order by 合計人数 desc, 都道府県コード asc
;