SELECT句の副問い合わせが上手くできません

Question

前提

現在 Oracle DB、SQLを用いて問題演習を行っています。
下記の問題演習が上手くできず困っているので教えていただきたいです。

各ポジションごと（GK、FWなど）に最も身長と、その選手名を表示してください。ただし、SELECT句に副問合せを使用してください。

問題の環境としてはMariaDBでできているで載っている回答は下記になります。

SELECT p1.position, MAX(p1.height) AS 最大身長, 
    (
    SELECT p2.name
    FROM players p2
    WHERE MAX(p1.height) = p2.height AND p1.position = p2.position
    ) AS 名前
FROM players p1
GROUP BY p1.position

実現したいこと

ここに実現したいことを箇条書きで書いてください。

• OracleDBで実行できるようにしたい。

発生している問題・エラーメッセージ

エラーメッセージ
ここではグループ関数は使用できません。

該当のソースコード

select 
  p1.position, 
  max(p1.height) as 最大身長,
  (
  select p2.name
  from players p2
  where p1.position = p2.position
    and max(p1.height) = p2.height
  ) as 名前
from players p1
group by position;

試したこと

副問い合わせで最大身長を表示するようにするなど方法は試したのですがうまくいきませんでした。

補足情報（FW/ツールのバージョンなど）

SI　Object Browser for Oracle 19
Oracle cliant 12.1.0

Comments

[sazi]

MySQLだと記述できる副問合せの内容なので、副問合せを理解するための問題としては不適切ですね。

[kou_99]

この問題ではOracleだとSELECT句で副問い合わせを使用するのは難しいということでしょうか？

[sazi]

MySQLはGroup byに関して色々忖度するので、標準SQLではエラーになる記述が出来たりするんです。
副問合せできなくもないけど冗長だし遅いと思われます。
そんな記述を覚えたいですか？

Answer 1

回答の SQL は、同一ポジションで最大の身長をもつプレーヤーが２人いると破綻しそうですね。

SELECT
    P1.POSITION
,   P1.HEIGHT
,   P1.NAME
FROM
    PLAYERS P1
INNER JOIN
    ( 
        SELECT 
            POSITION
        ,   MAX(HEIGHT) MAX_HEIGHT
        FROM
            PLAYERS
        GROUP BY
            POSITION
    ) P2
ON
    P1.POSITION = P2.POSITION
AND P1.HEIGHT   = P2.MAX_HEIGHT

追記

どうしても SELECT 句の中に副問い合わせを書きたいのであれば、副問い合わせが必ず１行以下になるような工夫が必要です。
下の例では名前の最大値をもってきています。

SELECT
    P1.POSITION
  , P1.MAX_HEIGHT
  , (SELECT MAX(P2.NAME) FROM PLAYERS P2 
      WHERE P2.POSITION = P1.POSITION 
	    AND P2.HEIGHT = P1.MAX_HEIGHT) NAME 
FROM
  ( 
    SELECT
        POSITION
    ,   MAX(HEIGHT) MAX_HEIGHT 
    FROM
        PLAYERS 
    GROUP BY
        POSITION
  ) P1

Comments

[kou_99]

Oracleだとこの問題でSELECT句で副問い合わせを使用するのは難しいということでしょうか？

[kou_99]

ありがとうございます！
特別な理由などがなければ他の方法を使用した方がよいということですね！
分かりやすくありがとうございました！