最大値を含むレコードの抽出

質問

あるテーブルから、最大値を持つレコードのキーと最大値だけでなく、そのレコードの値を出したいと思います。
最大値をもつレコードを抽出して、元のレコードと結合するしか方法はないのかなと思い、類似した質問があるような気もするのですが、条件が異なるとうまく行かないため、自分なりに考えてみました。
データベースはDB2です。

SELECT a.倉庫,a.品目,a.単価 FROM 在庫マスタ as a
where (a.品目,a.単価) in (select b.品目, max(b.単価)
from 在庫マスタ as b group by b.品目)
つまり、副問合せするときの結合キーが複数あるとSQLがエラーになってしまうようなのです。

結合キーが２つ以上ある時、このようなパターンの対処方法は無いでしょうか？

回答No.3

CHRONOS_0 · ベストアンサー

SELECT a.倉庫,a.品目,a.単価 FROM 在庫マスタ as a
where a.単価 in (select max(b.単価)
from 在庫マスタ as b where b.品目=a.品目)
でいいんじゃないの

お礼

有難うございました。
このようなシンプルな方法で実行できるのですね。
maxを求めるのに、GROUP BYを書かなくてもよい、というのが良く判りませんが、とにかくシンプルで早いですね。

回答No.4

chukenkenkou

#2回答者です。

一番簡潔なSQL（#3さん提示のSQL）を思いつかなかった自分が、ちょっと情けないです。

代わりに、もう一つ別のSQLを提示しておきます。
DB2は、分析関数をかなり前から実装しています。分析関数を使えば、ネストの深いサブクエリに比べ、代替手段として使える上に、性能も出すことが可能です。

＜例３＞
SELECT
x.倉庫,x.品目,x.単価
FROM
(SELECT
RANK() OVER(PARTITION BY 品目 ORDER BY 単価 DESC) AS rank,
倉庫,品目,単価
FROM 在庫マスタ) AS x
WHERE x.rank=1

お礼

有難うございました。

言葉足らずでしたが、自分の使っているのはOS400のDB2なので、
機能が少ないのかもしれません。
OVER句が使えないようです。

再三のご回答有難うございました。

回答No.2

chukenkenkou

数年前にDB2でのシステム開発支援をしたことがあるのですが、当時のDB2は、行値構成子（行値式）※1を使っての操作に制限がかなりありました。

※1 「where (列1,列2) 演算子 (値1,値2)」という条件式。
SQL-92で、標準SQLに取り入れられています。

行値構成子を使用しないSQL例を提示しますので、試してみてください。

＜例１＞
SELECT
a.倉庫,a.品目,a.単価
FROM 在庫マスタ AS a
WHERE EXISTS
(SELECT 1
FROM 在庫マスタ AS b
WHERE a.品目=b.品目
GROUP BY b.品目
HAVING a.単価=MAX(b.単価))

＜例２＞
SELECT
a.倉庫,a.品目,a.単価
FROM 在庫マスタ AS a
WHERE a.単価 >= ALL (SELECT b.単価
FROM 在庫マスタ AS b
WHERE a.品目=b.品目)

お礼

有難うございました。
何れも動作しました。
＜例２＞のほうが若干スピードは速いようです。
DB2でこのような書き方が出来るとは知りませんでした。

回答No.1

nyaa519323116

DB2はin述語で複数の要素のあるセットを扱えないのかな？　Oracleもそうだったような……DBMSによって方言があるようです。

手元にDBの環境がないので、自信ないけれども……

SELECT a.倉庫,a.品目,a.単価 FROM 在庫マスタ as a
where a.品目 in
(select b.品目 from 在庫マスタ as b where b.単価
in (select max(c.単価) from 在庫マスタ as c group by c.品目))

お礼

有難うございました。
正常に動作するようです。
ただし、副次問合せが多重なので、時間が掛かるようです。