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'
),
temp2
select
    t.PF_CODE as '都道府県コード', 
    p.PF_NAME as '都道府県名',
    sum(
        case when rank = 1 then n.NATION_NAME else 0 end 
    ) as 
人数が一番多い国のNATION_NAME → 1位 国名
人数が一番多い国のAMT → 1位 人数
人数が二番目に多い国のNATION_NAME → 2位 国名
人数が二番目に多い国のAMT → 2位 人数
人数が三番目に多い国のNATION_NAME → 3位 国名
人数が三番目に多い国のAMT → 3位 人数
各都道府県毎の外国人の合計人数 → 合計人数
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
where rank in (1, 2, 3)

;