FROM句にサブクエリを使わず、最大値 or 最小値 レコードを抽出する

よく忘れるのでメモ。
しょっちゅう「それあかん」ってMySQL から怒られる。

FROM句にサブクエリを使おうとするとダメ出しされる

例えばmy_table 内のvalue_field の値が最小値であるレコードだけを抽出したい場合。
何も考えずにサブクエリで横着するとこんな感じ。

SELECT
	b.*
FROM (
	SELECT 
		key_field,
		MIN(value_field) value_field
	FROM
		my_table
	GROUP BY
		group_field
)a
LEFT JOIN 
	my_table b 
ON
	a.group_field = b.group_field

しかし、これをMySQL でView として作成したい場合、FROM句にサブクエリを使おうとすると怒られます。

View’s SELECT contains a subquery in the FROM clause.

こんな感じで。

NOT EXISTS句でスクリーニングする

ダメ出しされるのはFROM句内でのサブクエリ使用なので、WHERE句を使ってレコードを抽出します。

CREATE VIEW 'hoge' 
SELECT 
	*
FROM 
	my_table a 
WHERE NOT EXISTS (
	SELECT 
		*
	FROM
		my_table
	WHERE NOT EXISTS
		group_field = a.group_field 
		AND
		value_field > a.value_field
)

上記のSQLでは、a.value_field より大きい値全てをWHERE NOT EXISTS で除外しているので「最大値」になります。
ちなみに、最小値を求めたい場合は、「>」を「<」に書き換えればOK。