WITH t AS 
(
  SELECT ROW_NUMBER () OVER ( 
        PARTITION BY f.PF_CODE ORDER BY AMT DESC, f.NATION_CODE ASC
    ) rn, f.PF_CODE, f.AMT, f.NATION_CODE, n.NATION_NAME
  FROM FOREIGNER as f, NATIONALITY as n
  WHERE f.NATION_CODE != 113 and f.NATION_CODE = n.NATION_CODE
)
SELECT p.PF_CODE as "都道府県コード", p.PF_NAME as "都道府県名", t1.NATION_NAME as "1位 国名", t1.AMT as "1位 人数", t2.NATION_NAME as "2位 国名", t2.AMT as "2位 人数", t3.NATION_NAME as "3位 国名", t3.AMT as "3位 人数", t4.sm as "合計人数"
FROM
  PREFECTURE as p, t as t1, t as t2, t as t3, (SELECT SUM(AMT) as sm, PF_CODE FROM t GROUP BY PF_CODE) as t4
WHERE
  t1.PF_CODE = p.PF_CODE and t1.rn = 1 and
  t2.PF_CODE = p.PF_CODE and t2.rn = 2 and
  t3.PF_CODE = p.PF_CODE and t3.rn = 3 and
  t4.PF_CODE = p.PF_CODE
ORDER BY t4.sm DESC, p.PF_CODE ASC