select
    f.pf_code as 都道府県コード,
    pf_name as 都道府県名,
    max(
        case
            when rank_result == 1 then nn
        end
    ) "1位 国名",
    max(
        case
            when rank_result == 1 then sum_amt
        end
    ) "1位 人数",
    max(
        case
            when rank_result == 2 then nn
        end
    ) "2位 国名",
    max(
        case
            when rank_result == 2 then sum_amt
        end
    ) "2位 人数",
    max(
        case
            when rank_result == 3 then nn
        end
    ) "3位 国名",
    max(
        case
            when rank_result == 3 then sum_amt
        end
    ) "3位 人数",
    sum(distinct amt) as 合計人数
from
FOREIGNER f
join PREFECTURE p on f.pf_code = p.pf_code
join
    (
        select
            f.pf_code pc,
            f.nation_code nc,
            n.nation_name nn,
            sum(f.AMT) as sum_amt,
            ROW_NUMBER() OVER(PARTITION BY f.pf_code ORDER BY sum(f.AMT) DESC, f.nation_code DESC) AS rank_result
        from
            FOREIGNER f
            join
                NATIONALITY n
            on  f.NATION_CODE = n.NATION_CODE
        where
            f.NATION_CODE != '113'
        group by
            f.pf_code,
            f.nation_code,
            nation_name
    )r on r.pc = f.pf_code
    
where 
f.NATION_CODE != '113'
group by
    f.pf_code
order by sum(amt) desc, f.pf_code