with tmp as(
select "NATIONALITY"."NATION_CODE","FOREIGNER"."PF_CODE","PREFECTURE"."PF_NAME","NATIONALITY"."NATION_NAME","FOREIGNER"."AMT",RANK() over (partition by "FOREIGNER"."PF_CODE" order by "FOREIGNER"."AMT" desc,"FOREIGNER"."NATION_CODE" asc) as "rank" from "FOREIGNER"
inner join "NATIONALITY" on "NATIONALITY"."NATION_CODE" = "FOREIGNER"."NATION_CODE" inner join "PREFECTURE" on "PREFECTURE"."PF_CODE" = "FOREIGNER"."PF_CODE" where "NATIONALITY"."NATION_CODE" <> '113'
)
select 
tmp."PF_CODE" as "都道府県コード",
tmp."PF_NAME" as "都道府県名",
max(case "rank" when 1 then "NATION_NAME" end) as "1位 国名",
max(case "rank" when 1 then "AMT" end) as "1位 人数",
max(case "rank" when 2 then "NATION_NAME" end) as "2位 国名",
max(case "rank" when 2 then "AMT" end) as "2位 人数",
max(case "rank" when 3 then "NATION_NAME" end) as "3位 国名",
max(case "rank" when 3 then "AMT" end) as "3位 人数",
sum("AMT") as "合計人数"
from tmp 
group by "都道府県コード","都道府県名"
order by "合計人数" desc, "都道府県コード" desc