SELECT
    RK.PF_CODE AS '都道府県コード'
    , PR.PF_NAME AS '都道府県名'
    , NA1.NATION_NAME AS '1位 国名'
    , MAX(NO1A) AS '1位 人数'
    , NA2.NATION_NAME AS '2位 国名'
    , NO2A AS '2位 人数'
    , NA3.NATION_NAME AS '3位 国名'
    , NO3A AS '3位 人数'
    , TOTAL AS '合計人数'
FROM
    (SELECT
        F01.PF_CODE AS PF_CODE
        , F01.NATION_CODE AS NO1N
        , F01.AMT AS NO1A
        , LAG(F01.NATION_CODE, 1) 
            OVER (PARTITION BY F01.PF_CODE ORDER BY F01.AMT ASC, F01.NATION_CODE ASC) AS NO2N
        , LAG(F01.AMT, 1) 
            OVER (PARTITION BY F01.PF_CODE ORDER BY F01.AMT ASC, F01.NATION_CODE ASC) AS NO2A
        , LAG(F01.NATION_CODE, 2) 
            OVER (PARTITION BY F01.PF_CODE ORDER BY F01.AMT ASC, F01.NATION_CODE ASC) AS NO3N
        , LAG(F01.AMT, 2) 
            OVER (PARTITION BY F01.PF_CODE ORDER BY F01.AMT ASC, F01.NATION_CODE ASC) AS NO3A
        , SUM(FT.AMT) AS TOTAL
    FROM
        FOREIGNER AS F01
        INNER JOIN FOREIGNER AS FT
            ON FT.PF_CODE = F01.PF_CODE
    WHERE
        F01.NATION_CODE <> '113'
        AND FT.NATION_CODE <> '113'
    GROUP BY
        F01.PF_CODE
        , F01.NATION_CODE
        ) AS RK
    INNER JOIN PREFECTURE AS PR
        ON PR.PF_CODE = RK.PF_CODE
    INNER JOIN NATIONALITY AS NA1
        ON NA1.NATION_CODE = RK.NO1N
    INNER JOIN NATIONALITY AS NA2
        ON NA2.NATION_CODE = RK.NO2N
    INNER JOIN NATIONALITY AS NA3
        ON NA3.NATION_CODE = RK.NO3N
GROUP BY 
    RK.PF_CODE
ORDER BY
    RK.TOTAL DESC
    , RK.PF_CODE ASC