SELECT DISTINCT
	 F.PF_CODE AS 都道府県コード
	,RANKING.PF_NAME AS 都道府県名
	,FIRST_VALUE(RANKING.NATION_NAME) OVER (PARTITION BY RANKING.PF_CODE ORDER BY RANKING.JUNI ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)   AS [1位 国名]
	,FIRST_VALUE(RANKING.AMT) OVER (PARTITION BY RANKING.PF_CODE ORDER BY RANKING.JUNI ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [1位 人数]
	,NTH_VALUE(RANKING.NATION_NAME,2) OVER (PARTITION BY RANKING.PF_CODE ORDER BY RANKING.JUNI ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [2位 国名]
	,NTH_VALUE(RANKING.AMT,2) OVER (PARTITION BY RANKING.PF_CODE ORDER BY RANKING.JUNI ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [2位 人数]
	,NTH_VALUE(RANKING.NATION_NAME,3) OVER (PARTITION BY RANKING.PF_CODE ORDER BY RANKING.JUNI ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [3位 国名]
	,NTH_VALUE(RANKING.AMT,3) OVER (PARTITION BY RANKING.PF_CODE ORDER BY RANKING.JUNI ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  AS [3位 人数]
	,SUM(RANKING.AMT) OVER (PARTITION BY F.PF_CODE)  AS [合計人数]
FROM FOREIGNER AS F
INNER JOIN
(
	SELECT
		 F.PF_CODE
		,F.NATION_CODE 
		,P.PF_NAME
		,N.NATION_NAME
		,RANK() OVER ( PARTITION BY F.PF_CODE ORDER BY SUM( AMT ) DESC ) AS JUNI
		,SUM(AMT) AS AMT
	FROM FOREIGNER AS F
	INNER JOIN PREFECTURE AS P
	ON  F.PF_CODE = P.PF_CODE
	INNER JOIN NATIONALITY AS N
	ON  F.NATION_CODE = N.NATION_CODE
	WHERE F.NATION_CODE <> '113'
	GROUP BY F.PF_CODE
		,F.NATION_CODE 
		,P.PF_NAME
		,N.NATION_NAME
) AS RANKING
ON  F.PF_CODE  = RANKING.PF_CODE
AND F.NATION_CODE = RANKING.NATION_CODE
GROUP BY F.PF_CODE,RANKING.PF_NAME,RANKING.NATION_NAME 
ORDER BY [合計人数] DESC, [都道府県コード] ASC