WITH ordered AS (
	SELECT
		pf_code,
		pf_name,
		nation_code,
		nation_name,
		sum(amt) AS sum_amt
	FROM
		foreigner,
		prefecture USING (pf_code),
		nationality USING (nation_code)
	WHERE
		nation_code <> 113
	GROUP BY
		pf_code,
		nation_code
	ORDER BY
		sum(amt)
		DESC
)
SELECT
	pf_code AS "都道府県コード",
	pf_name AS "都道府県名",
	(
		SELECT
			nation_name
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 1) AS "1位 国名",
	(
		SELECT
			sum_amt
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 1) AS "1位 人数",
	(
		SELECT
			nation_name
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 1,
		1) AS "2位 国名",
	(
		SELECT
			sum_amt
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 1,
		1) AS "2位 人数",
	(
		SELECT
			nation_name
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 2,
		1) AS "3位 国名",
	(
		SELECT
			sum_amt
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 2,
		1) AS "3位 人数",
	(
		SELECT
			sum(sum_amt)
		FROM
			ordered
		WHERE
			ordered.pf_code = foreigner.pf_code
		LIMIT 3) AS "合計人数"
FROM
	foreigner,
	prefecture USING (pf_code), nationality USING (nation_code)
GROUP BY
	pf_code
ORDER BY
	"合計人数" DESC, pf_code