日付の最大値を検索条件にする方法

質問

お世話になります。

ORACLEのMAX()関数を使用して、
下記内容を取得するSQLを作成しています。

・商品テーブルから商品コードを取得したい
・上記の取得条件として、商品テーブルの日付項目が最新日付のレコードのみを
取得したい
・上記の取得条件に加えて、指定したいくつかの商品に該当するもののみを
検索対象としたい

【SQLサンプル】
SELECT
商品コード
FROM
商品テーブル
WHERE
商品コード IN (101, 102, 103)
AND 在庫区分 = '0'
AND 日付 = (SELECT MAX(日付) FROM 商品テーブル
WHERE 商品コード IN (101, 102, 103)
AND 在庫区分 = '0'
)
AND ランク = (SELECT MAX(ランク) FROM 商品テーブル
WHERE 商品コード IN (101, 102, 103)
AND 在庫区分 = '0'
)

上記以外の書き方で何か良い書き方があれば、
ご教授願えますか？　よろしくお願いいたします。

回答No.2

Siegrune · ベストアンサー

データの内容がわからないのではっきりとはいえませんが、
データの内容によっては、質問のSQLはまずいです。

商品コード,日付
101,5/5
102,5/6
103,5/4
とあると、102しかとってこないのでは？

普通は、
SELECT
商品コード
FROM
商品テーブル X
WHERE
商品コード IN (101, 102, 103)
AND 在庫区分 = '0'
AND 日付 = (SELECT MAX(日付) FROM 商品テーブル Y
WHERE X.商品コード = Y.商品コード
AND Y.在庫区分 = '0'
)
とします。
で、もう１点問題があって
ランク
の部分です。
商品コード,日付,ランク
101,5/5,3
101,5/6,2
とあるとどちらを優先ですか？

このパターンがないとして、
商品コード,日付,ランク
101,5/5,3
101,5/5,2
101,5/5,1
のようなデータが入っているので、
最大の日付を優先とすると、

AND X.ランク = (SELECT MAX(ランク) FROM 商品テーブル Z
WHERE X.商品コード = Z.商品コード
AND Z.在庫区分 = '0'
AND Z.日付 = (SELECT MAX(日付) FROM 商品テーブル Q
WHERE Q.商品コード = Z.商品コード
AND Q.在庫区分 = '0'
)
)
とでもしますか。

別の方法

SELECT
X.商品コード
FROM
商品テーブル X,
(
SELECT
商品コード,MAX(日付) as 日付
FROM
商品テーブル
WHERE
商品コード IN (101, 102, 103)
AND 在庫区分 = '0'
group by 商品コード) Y,
(
SELECT
商品コード,日付,MAX(ランク) as ランク
FROM
商品テーブル
WHERE
商品コード IN (101, 102, 103)
AND 在庫区分 = '0'
group by 商品コード,日付) Z
WHERE x.商品コード IN (101, 102, 103)
AND x.在庫区分 = '0'
AND x.商品コード = Y.商品コード
AND x.日付 = Y.日付
AND x.商品コード = Z.商品コード
AND Y.日付 = Z.日付
AMD x.ランク = Z.ランク

という方法もあります。
（副問い合わせで抽出された結果のデータ量が多いとリソースを食うし、レスポンスは悪いですが。）

回答No.1

yamada_g

>・上記の取得条件として、商品テーブルの日付項目が最新日付のレコードのみを取得したい
とありますが、記載のSQLは「日付が最新」かつ「ランクが最大」となっていますよね。
とりあえず、記載のSQLをベースにしました。
ORACLEのバージョンが書いていないのですが、分析関数が使える（9i？）なら。

SELECT
商品コード
FROM (
SELECT
商品コード,日付,ランク
,MAX(日付) OVER() MAX日付
,MAX(ランク) OVER() MAXランク
FROM
商品テーブル
WHERE
商品コード IN (101, 102, 103)
AND 在庫区分 = '0')
WHERE
日付 = MAX日付 AND ランク = MAXランク;

とか。