SQLの質問です。

質問

下記のようなテーブルで、　レベル4以下で点数が入力されているMAXの行を持ってくるにはどうしたらよいでしょうか？よろしくお願いいたします。

職員番号 氏名 レベル 点数
01000 山田　花子 4
01000 山田　花子 3 40
01000 山田　花子 2 30
01000 山田　花子 1 20
01100 田中　あああ 4 10
01100 田中　あああ 3 20
01100 田中　あああ 2 30
01100 田中　あああ 1 40
01200 佐藤　あああ 4
01200 佐藤　あああ 3
01200 佐藤　あああ 2 30
01200 佐藤　あああ 1 40

この場合欲しい結果は　職員番号01000の人は点数40点の行、01100の人は点数10点の行、01200の人は点数30点の行　　3行を表示したいです。

回答No.2

mitoneko · ベストアンサー

select 職員番号, 氏名, レベル, 点数
from aaaa a0
where 点数 is not null and レベル <= 4 and
a0.レベル=(select max(a1.レベル)
from aaaa a1
where a1.職員番号 = a0.職員番号 and 点数 is not null and レベル<=4) and
a0.点数 = (select max(a2.点数)
from aaaa a2
　　　　　　　　　　where a0.職員番号 = a2.職員番号 and　
　　　　　　　 　　　a0.レベル = a2.レベル)
order by 職員番号

　ウィンドウ構文を使わずにやろうとすると、こんな感じでしょうか。
　なお、もし、（職員番号，レベル）の組に重複がないのであれば、where区内の２つめのselect文を含む条件( a0.点数=で始まる比較）は不要です。（ちなみに、一応実験は、01100でレベル４で得点5点を追加して行っています。）
　また、常識的に、職員番号が同じで氏名が違うなんてことはあり得ないとしていますが、もしこれを考慮に入れ別人として扱う必要があるなら、その条件は追加が必要です。

回答No.1

nora1962

> 下記のようなテーブルで、　レベル4以下で点数が入力されているMAXの行を持ってくるには
> どうしたらよいでしょうか？よろしくお願いいたします。

これだけなら、仮にテーブル名を「AAA」とした時

select 職員番号,氏名,レベル,点数
from (
select AAA.*,
rank() over( partition by 職員番号 order by 点数 desc ) rnk
from AAA
where AAA.レベル<=4 and AAA.点数 is not null ) Q
where rnk=1;

で求められますが。
望まれている
> この場合欲しい結果は　
> 職員番号01000の人は点数40点の行、
> 01100の人は点数10点の行、
> 01200の人は点数30点の行　　
> 3行を表示したいです。
にはなりません。そもそも、職員番号「01100」の場合レベル「1」で、点数が「40」の人がいるので
質問内容からすると、そちらが優先になります。
select 職員番号,氏名,レベル,点数
from (
select AAA.*,
rank() over( partition by 職員番号 order by レベル desc, 点数 desc ) rnk
from AAA
where AAA.レベル<=4 and AAA.点数 is not null ) Q
where rnk=1;
とすると、例示された結果は出ますが。要件としてどっちが正しいのでしょうか。

補足

ご回答ありがとうございます。

質問があいまいで申し訳ありませんでした。

正確には　レベルが4以下で点数が入力されているMAXのレベルの行を持ってきたいです。

レベル４に点数がなかったらレベル３の行　レベル３にも点数の入力が無かったらレベル２の行を
表示といった感じです。