MySQLでサブクエリを使用せずGROUP BYとJOINを併用したい

MySQLのサブクエリを使わずに、あるデータを取得したいのですがうまくクエリを作成できません。
もしかするとその考え方自体がナンセンスなのかもしれませんが、もしアドバイスをいただけたら嬉しいです。
今回使用しているテーブル構成が分かるように下記にキャプチャを貼ります。(データの行数が多いので一部を抜粋しています)

やりたいこと

サブクエリを使わずに、playersテーブルから、各ポジションごとに一番身長が高いプレイヤーを取得したい
取得するcolumnはposition, MAX(height), name

以下のようにサブクエリを使えば、うまく取得できます

SELECT p1.position, p1.aaa, p2.name 
FROM (SELECT position, MAX(height) AS aaa FROM players GROUP BY position) p1 
JOIN players p2 
ON p1.position = p2.position 
AND p1.aaa = p2.height;

データを取得できないクエリ

SELECT p1.position, MAX(p1.height), p2.name 
FROM players p1 
GROUP BY position 
JOIN players p2 
ON p1.position = p2.position 
AND MAX(p1.height) = p2.height;

自分では、こちらのクエリでも、サブクエリ同様にGROUP BYでまとめたp1テーブルにp2テーブルをJOINしているのでサブクエリを使わなくてもデータ取得が行えると思いましたがsyntax errorで取得できませんでした。

もしわかる方がいましたら教えていただきたいです。
よろしくお願いします。

m.ts10806

2019/07/13 09:37

MySQLのバージョンは幾つでしょうか。ただ、これはサブクエリが必須ではないかと私は思います。

tmykndr

2019/07/13 10:24

回答いただきありがとうございます！やはりこの場合ですと必須なのですかね。今出先のためMySQLのバージョンの確認難しいので、確認でき次第、載せさせていただきます。

m.ts10806

2019/07/13 10:36

こちらは回答ではなく質問への追記修正依頼のコメント欄です。

行動規範の内容に同意します

回答2件

ベストアンサー

自分では、こちらのクエリでも、サブクエリ同様にGROUP BYでまとめたp1テーブルにp2テーブルをJOINしているのでサブクエリを使わなくてもデータ取得が行えると思いましたがsyntax errorで取得できませんでした。

はい、普通にJOINを行うと、それはGROUP BYより前に処理されます。GROUP BYの結果とJOINするには、「GROUP BYの結果」全体をサブテーブルにする必要があります。

投稿2019/07/13 10:58

maisumakun

総合スコア145184

tmykndr

2019/07/13 11:11

回答ありがとうございます！！なるほど理解できました！！だからサブクエリじゃないと取得できなかったのですね。とてもスッキリしました！ありがとうございました！m()m

行動規範の内容に同意します

SQLでは原則として、GROUP BY 句の要素以外は集計値しかSELECTできません。
ですからサブクエリ(のようなものも含めれば)は必須です。

投稿2019/07/13 09:58

hihijiji

総合スコア4150

tmykndr

2019/07/13 10:44

回答いただきありがとうございます！！回答いただいた内容について質問させてくださいm()m > GROUP BY 句の要素以外は集計値しか SELECT できません。今回 SELECT には3つのcolumnを指定していますが、GROUP BY しているのは p1テーブルなので、SELECT p1.position, MAX(p1.height), としています。（この2つのcolumnはGROUP BYで取得できるcolumnのルールに反していないはず）そしてもう一つ SELECT 句に書いてある p2.name は GROUP BY していない p2テーブルをJOIN して取得しようと思い、あのクエリを書きました。 GROUP BY を使用した時にSELECTできるルールについては知っていたので、その上でクエリを書いたのですが、どこかで認識間違いをしているんですかね。。。もし考え方自体が間違っていたら、教えていただけるととても嬉しいです。よろしくお願いします。