select
PF_CODE,
PF_NAME,
NATION_NAME,
AMT,
RANK
from(
select
PREFECTURE.PF_CODE,
PREFECTURE.PF_NAME,
NATIONALITY.NATION_NAME,
SUM(FOREIGNER.AMT) as AMT,
rank() over(PARTITION BY PREFECTURE.PF_CODE,PREFECTURE.PF_NAME order by SUM(FOREIGNER.AMT) desc) as RANK
from FOREIGNER
inner join NATIONALITY
on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
inner join PREFECTURE
on FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
where NATIONALITY.NATION_NAME <> "その他"
group by PREFECTURE.PF_CODE, PREFECTURE.PF_NAME, NATIONALITY.NATION_NAME
)
where RANK <= 3