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の合計値の内、上位25件を
取得したいのですが...

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はとおりますが
条件が変わってしまいます。
抽出条件でマージ、降順ソートしたものに対して上位n件の取得をするには
どうすれば良いのでしょうか?

このQ&Aに関連する最新のQ&A

A 回答 (4件)

一応できそうな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位になるような絞込みと、降順の並び替えをしています。

もう少し、スマートな文もあるでしょうが、あとはご自分でがんばってください。
    • good
    • 0
この回答へのお礼

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

お礼日時:2001/02/26 09:07

うーん、必ず1回のSELECT文で、25位まで取得しなくてはいけないんですか?


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

# それじゃダメだから、質問しているんでしょうか...?
    • good
    • 0
この回答へのお礼

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

お礼日時:2001/02/26 09:09

この問題はOracle8までだと、相当苦労します。


8i以降だとうまくいくのですが・・・

特に降順なので難しいと思います。ビューを使うのが手ではないでしょうか?
    • good
    • 0
この回答へのお礼

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

お礼日時:2001/02/23 08:58

そうですね。

複問合わせの中に 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を使わなければならないのではないでしょうか?

補足日時:2001/02/21 13:05
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aと関連する良く見られている質問

QSQLiteManagerでOrder by DESCが使えない

Webベース型のSQLite管理ソフトを使おうとしているのですが、
なぜか Order by " " Descを打ちこんでも、
Order by " " Asc として認識されます。

ネットで検索しても類似例は見つからなくて、困っています。

なにか解決策ご存じの方はいませんか?

Aベストアンサー

どこで配布されているソフトでしょう?
配布元にバグ情報とか、QandAとか、質問受付ページとかないのですか?

Qfrom...where を from...join..on にするには

以下のSQL文は from ... where ... で書かれていますが
これを from ... join ... on ... で書き直したいのですが可能でしょうか?

select X.col1, U.col1

from TABLE_X as X ,TABLE_U as U

where U.REC_NO = (select B.REC_NO
from TABLE_A as A, TABLE_B as B
where A.ITME = B.ITEM
and X.DATA = A.DATA
fetch first 1 row only )

むずかしくしているのは、fetch first 1 row only なのですが
これがないと where句のかっこのなかのselect文は複数の結果を返します
しかし、その複数の結果はすべて同じものになることがデータ上保証されています

fetch first を除いて select distinct としても同じ結果となりますが
パフォーマンスがひどく低下してしまいます。

以下のSQL文は from ... where ... で書かれていますが
これを from ... join ... on ... で書き直したいのですが可能でしょうか?

select X.col1, U.col1

from TABLE_X as X ,TABLE_U as U

where U.REC_NO = (select B.REC_NO
from TABLE_A as A, TABLE_B as B
where A.ITME = B.ITEM
and X.DATA = A.DATA
fetch first 1 row only )

むずかしくしているのは、fetch first 1 row only なのですが
これがないと w...続きを読む

Aベストアンサー

on句は結合条件でfetch firstは抽出条件なのでwhere句をon句にする単純な書き直
しはできないと思います。やるとしたら副選択のselect文をfrom句に書いて一時テーブルとしてfetch firstをdistinctにするしかないような気がします

select X.col1, U.col1
from
(select distinct B.REC_NO
from TABLE_A as A, TABLE_B as B
where A.ITME = B.ITEM
and X.DATA = A.DATA) as Z
inner join TABLE_X as X on ...
inner join TABLE_U as U on ...

fetch first にくらべて distinct なので遅いような気がしますが
実際には副選択がないためにそための内部的に作られる一時表が軽くなるのでこちらのほうが速いと思います。

Qクロス集計での order by句の使い方

下記の order byの部分を年月日でなく合計件数順に並べ変えたいのですが、Count(氏名)を入れると「 order by句で集計関数は使えません」のようなメッセージが出ます。(access2000)
何か良い方法はないでしょうか。

TRANSFORM Count(氏名) AS 氏名のカウント
SELECT 年月日, Count(氏名) AS 合計件数
FROM 日誌
GROUP BY 年月日
ORDER BY 年月日
PIVOT 区分;

#access素人です。

Aベストアンサー

こんにちは。

クロス集計クエリを元に、もう1つクエリを作って
「氏名のカウント」で並び替えをしてみてはどうでしょうか?

ご参考までに。

ではでは・・・

Qorder by について

質問させてください。
下記テーブルがあるとします。
[samle_tbl](フィールドa, b(integer))
======
a|b
======
2|*
*|4
3|5
4|2
*|1
2|6
1|2
(※*はNULLデータ)
上記のテーブルでa, bフィールドにおいて数字の大きいレコード順に取得したいとかんがえています。
2|6
3|5
*|4
4|2
2|*
1|2
*|1
簡単にいうとa, bフィールドの値においてどちらかの数値がa, bフィールド内において降順にレコードをシュとくしたいです。
同じ順番に値する場合*(NULLデータ)を含むレコードと含まないレコードの順番は気にしません。
どのようなSQLを実行すれば取得できますでしょうか?

Aベストアンサー

order by
case
when coalesce(a,-9999) > coalesce(b,-9999) then a else coalesce(b,-9999)
end


とかどうでしょう?
だめだったらasで別名定義も試してみてください。
#nullは適当に-9999なんてしてみました(^^;

QORDER BY の項目を SELECT する効果

ORDER BY の項目を SELECT する項目に含めるか否かで、速度や
メモリ消費の点で何らかの差がでるでしょうか?どちらが望ましい
という点も含めてプロフェッショナルの方の回答をお待ちしています。

Aベストアンサー

長い間、標準SQL及び多くのRDBMSでは、「ORDER BYで指定する項目は、SELECTの選択リストで指定した項目でなければならない」という制限がありました。
「SELECTの選択リストで指定していない項目でも、ORDER BY指定が可能」となったのは、比較的、最近のことです。
現在でも、「ORDER BYで式を指定」した場合などで、RDBMSにより、最適なアクセス計画を生成できないといった問題があります。

記述したSQLで、性能が出せるかどうかは、使用しているRDBMS、バージョンにより、大きな違いが生じます。
ORDER BYに関しては、メモリ消費よりは、インデクスを有効活用し、ソートを抑止できるかどうかが、利用者側にはっきり見えるところです。

自分で使うRDBMS及びバージョンで、どういうアクセス計画が生成されるかという、分析は必須です。


このカテゴリの人気Q&Aランキング

おすすめ情報