SELECT
    PR.PF_CODE AS "都道府県コード"
    , PR.PF_NAME AS "都道府県名"
    , (SELECT NA1.NATION_NAME FROM FOREIGNER AS F1
    INNER JOIN NATIONALITY AS NA1 
        ON FO.NATION_CODE = NA.NATION_CODE WHERE F1.PF_CODE = FO.PF_CODE ORDER BY F1.AMT LIMIT 1, 0)  AS "1位 国名"
        
    , (SELECT F1.AMT FROM FOREIGNER AS F1
    INNER JOIN NATIONALITY AS NA1 
        ON FO.NATION_CODE = NA.NATION_CODE WHERE F1.PF_CODE = FO.PF_CODE ORDER BY F1.AMT LIMIT 1, 0)  AS "1位 人数"
    
    , NA.NATION_NAME AS "2位 国名"
    , FO.AMT AS "2位 人数"
    , NA.NATION_NAME AS "3位 国名"
    , FO.AMT AS "3位 人数"
    , SUM(AMT) AS "合計人数"
FROM
    PREFECTURE AS PR 
    INNER JOIN FOREIGNER AS FO 
        ON PR.PF_CODE = FO.PF_CODE  
    INNER JOIN NATIONALITY AS NA 
        ON FO.NATION_CODE = NA.NATION_CODE 
        AND NA.NATION_CODE != '113' 
WHERE
    1
GROUP BY PR.PF_CODE
ORDER BY
    "合計人数" DESC
    , 都道府県コード ASC;