MySQL　グループの中の最大値のデータを取得

MySQL

MySQL　グループの中の最大値のデータを取得

複数のデータがあるとき、グループで区切った、グループごとの最大値のデータを取得したいケースがよくあると思います。
文章で書いてもよくわからないと思うので、例をあげてみます。

ここに2つのテーブルがあります。（いつものですが…）
ninjyaテーブルと、skillテーブルです。
ninjyaテーブルには、忍者の名前、skillテーブルには必殺技の名前が格納されています。
一人の忍者は多くの必殺技を持つという、1対他のデータ構造になっています。
今回は、それぞれの必殺技に、威力(power)というフィールドを追加してみました。

|>|ninjya テーブル|h
|id|name|h
|1|ナルト|
|2|サスケ|

#br

|>|>|>|skill テーブル|h
|id|ninjya_id|name|power|h
|1|1|螺旋丸|100|
|2|1|ナルトキック|10|
|3|2|千鳥|10|
|4|2|天照|110|
|5|1|影分身|3|
|6|2|月読|5|

では、skillテーブルから、それぞれの忍者ごとの最大の威力のデータを取得します。

 SELECT *
 FROM skill
 WHERE power 
 	IN 
 	(SELECT MAX(power) FROM skill GROUP BY ninjya_id)

結果です。

|id|ninjya_id|name|power|h
|1|1|螺旋丸|100|
|4|2|天照|110|

なんだか

 SELECT MAX(power) FROM skill GROUP BY ninjya_id

だけでいけそうですが、上記のSQLを下記のように実行すると、次の通りです。MAX( power )だけ取得してもしょうがないので、他のデータも（*）で取得してみます。

 SELECT * , MAX( power ) FROM skill GROUP BY ninjya_id LIMIT 0 , 30

結果は次の通りです。

|id|ninjya_id|name|power|MAX( power)|h
|1|1|螺旋丸|100|100|
|3|2|千鳥|10|110|

ninjya_idが2の場合の、powerが最大の技の名前が違いますね。
確かに、威力の最大値MAX( power)は正しいですが、そのほかのデータが一緒にとれません。
「グループの中の最大値のデータを取得」したいのであって、「グループの中の最大値を取得」したいわけではないんですね。（紛らわしいですが）

せっかくなので、忍者の名前と連結させておきます。
エイリアスをつける必要があります。

 SELECT * 
 FROM
 (

SELECT *
FROM skill
WHERE power
IN
(SELECT MAX(power) FROM skill GROUP BY ninjya_id)
)

 AS max_power
 JOIN
 	ninjya 
 ON
 	ninjya.id=max_power.ninjya_id

結果は次の通りです。

|id|ninjya_id|name|power|id|name|h
|1|1|螺旋丸|100|1|ナルト|
|4|2|天照|110|2|サスケ|

• max_powerで各忍者の最も強い必殺技が取得できてるのは偶然では？例えば、螺旋丸のpowerが110で、ナルトキックのpowerが200の場合、max_powerで取得されるのは螺旋丸、ナルトキック、天照になるので、どちらがナルトの最も強い必殺技として選ばれるか、保証が無いと思います。 — 匿名 {2015-10-03 (土) 08:47:24}
• 匿名さん、こんにちは。すみません、同じ値が入った時に、ってことですかね？すみませんが、ここでは「グループの中の最大値のデータを取得」のサンプルだけなので、そこまでは考慮していませんでした。ご指摘ありがとうございます。 — 書いた人 {2015-12-21 (月) 19:03:15}