SELECT
MAIN.PF_CODE,
pref.PF_NAME,
RANK(MAIN.PAR_NAT_AMT) OVER (PARTITION BY MAIN.PF_CODE, MAIN.NATION_CODE ORDER BY MAIN.PAR_NAT_AMT)
FROM (
SELECT 
for.PF_CODE,
for.NATION_CODE,
SUM(for.AMT) AS PAR_NAT_AMT
FROM FOREIGNER AS for
GROUP BY for.PF_CODE, for.NATION_CODE
) AS MAIN
INNER JOIN PREFECTURE AS pref
ON pref.PF_CODE = MAIN.PF_CODE
INNER JOIN NATIONALITY AS nat
ON nat.NATION_CODE = MAIN.NATION_CODE
WHERE MAIN.NATION_CODE <> '113'  -- その他
-- GROUP BY for.PF_CODE