ＯｒａｃｌｅＳｑｌで『RORNUM』の使い方について

質問

VB6でコーディングをしています
DBはOracle7.3です

SELECT　A.One,　A.Two,　SUM(A.Three)　Three,　
FROM　A　
WHERE　(　A.One　=　'001'　AND　A.Two　=　'3')　
GROUP　BY　A.One,　A.Two
ORDER　BY　Three　DESC　

上記のようにAテーブルの項目One,Two,Threeを抽出するSQLで
重複項目One,Twoでグループ化したThreeの合計値の内、上位２５件を
取得したいのですが．．．

SELECT　*　
FROM　(
SELECT　A.One,　A.Two,　SUM(A.Three)　Three,　
FROM　A　
WHERE　(　A.One　=　'001'　AND　A.Two　=　'3')　
GROUP　BY　A.One,　A.Two
ORDER　BY　Three　DESC　)
WHERE　ROWNUM　<=　25

上記のように記述するとORDER　BY句でエラーになります
ORDER　BY句をWHERE　ROWNUMの下に記述するとSQLはとおりますが
条件が変わってしまいます。
抽出条件でマージ、降順ソートしたものに対して上位ｎ件の取得をするには
どうすれば良いのでしょうか？

回答No.4

misoka

うーん、必ず１回のSELECT文で、25位まで取得しなくてはいけないんですか？
msystemさんもおっしゃっていますが、大抵はカーソルをつくって、25回ループさせて取得すると思います...
VB6ってことは、ミドルウェアはoo4oでしょうか？
oo4oならPL/SQLブロックをDBに投げて、ParamArray（だったっけ？）で値をもってこれるような気がしました。（これってOracle8の話なのかなぁ？ 詳しくはoo4oのドキュメントを参照してください）
それか、ダイナセットをつくってループさせるっていうのが、多分いちばん単純で簡単です（こっちだとちょっと効率悪い？）。

# それじゃダメだから、質問しているんでしょうか...？

お礼

プログラムが複数本あるため、このプログラムに関してだけＰＬ／ＳＱＬを使用するのは一寸抵抗があります。
でも、貴重なお時間をさいて頂いて申し訳ありませんでした。

回答No.3

msystem

一応できそうなSQL文がありますが、Oracleの動作保証がないため、VBのほうで上位25位をとることをお勧めします。

下にそのSQLを記述します。
まず、例で書いていただいたSQL文は必ず1行しか返ってこないのが気になりますが取りあえず無視して進めます。
まず、ソート用のテーブルを作成します。A.ThreeはNumber型だとします。
create table x (a number);
何でもいいので1行追加します。
insert into x values(0);
commit;
この準備ができたところで、以下のSQL文を投げます。
select d.one,d.two,d.three from
(select c.one,c.two,c.three,c.r,max(r) m from
(select b.one,b.two,b.three,rownum r from
(select a.one,a.two,sum(a.three) three from a
where (a.one='001' and a.two='3')
group by a.one,a.two) b,x
where b.three=x.a(+)) c
group by c.one,c.two,c.three,c.r) d
where r>=m-25
order by r desc;
多分、これで25位までの降順で結果が返ってくるはずです。

解説すると、一番内側のサブクエリが合計を出すクエリです。（質問のあったクエリ。ただし、この文だと1行しか返らない）
2番目のサブクエリで、外部結合させることにより並び替えをしています。それと同時に昇順の番号（r）をつけています。（ただし、これは副作用的に並び替えができているだけで、Oracleの保証はありません。これで昇順に並び代わります）
3番目のクエリで、昇順番号の最大値をとっています。
一番外側のSelect文で大きいほうから25位になるような絞込みと、降順の並び替えをしています。

もう少し、スマートな文もあるでしょうが、あとはご自分でがんばってください。

お礼

とってもややこしいＳＱＬをわざわざ考えて下さってありがとうございました(笑)
が、ソートテーブルを作成するやり方は一寸いただけないので、ここは諦めて全件抽出後にＶＢで２５回ループする事にします。

回答No.2

msystem

この問題はOracle8までだと、相当苦労します。
8i以降だとうまくいくのですが・・・

特に降順なので難しいと思います。ビューを使うのが手ではないでしょうか？

お礼

御回答ありがとうございました
ビューを使うと言う事ですが、毎回抽出条件が変わるため使えません
何か効率の良い手はないものでしょうか？

回答No.1

noname#1802

そうですね。複問合わせの中に order by は使えません。

●回答（？）例
SELECT　A.One,　A.Two,　SUM(A.Three)　Three
FROM　A

の A.One の前に順序を発生させ ( i = 1,2,3,4,5,・… )
HAVING で i <= 25
でどうでしょう？

実際SQL文を動かしたわけではないので適当です。
すいません。。

補足

sub　queryにorder　by句を記述できるのはoracle8以上なんですよね！
御回答いただいたHAVINGの例ですが、順序を発生させるとは？
実際にSQLのコーディングはどのようになるのでしょうか？
順序をとるとなると、やはりrounumを使わなければならないのではないでしょうか？