Group By を使用して任意のカラムの最大値のデータを取得する。

Group By を使用して任意のカラムの最大値のデータを取得する。

次のようなテーブルデータがあるとします。

　　ID　　　　店舗CD　　　　適用日付
　　A001　　　101　　　　　 20100701
　　A002　　　101　　　　　 20100801
　　A003　　　101　　　　　 20100901
　　B001　　　102　　　　　 20100701
　　B002　　　102　　　　　 20100801
　　B003　　　102　　　　　 20100901


抽出イメージですが、このテーブルデータの店舗コードをGroupByして、MAX(適用日付)
としたときにIDが取得できなくなります。
なのでGroupByしたときの日付最大値のデータのIDを取得したいのですがいい方法が浮かびません。
　同じテーブルデータでGroupBy(店舗コード)、MAX(適用日付)としたサブクエリを(店舗CD=店舗CD）、(適用日付=適用日付)とInnerJoinする方法も考えましたが、万が一、適用日付と店舗CDが同一のものがあった場合、店舗CDが重複してしまいます。
　各店舗CDの最大適用日付のID取得したいのです。
なのでイメージはGroupby(店舗CD)とMAX(適用日付)とした時にMAX(適用日付)のIDを取得し、
なおかつ欲をいえば、MAX(適用日付)のIDが複数存在する場合は、１店舗CDにつき一つの適用日付を取得したいです。(この際、IDはなんでもいいです。)

簡単そうなことですごく悩んでいます。
どなたか助けてください。
よろしくお願いします。

No.5 ベストアンサー 回答者： k_o_r_o_c_h_a_n 回答日時： 2010/07/24 09:28

select ID,店舗CD,適用日付

from
(select x.*,row_number() over(partition by 店舗CD order by 適用日付 desc) R from TBL x)
where R=1
;
とか。

この回答への補足

ご回答ありがとうございます。
この方法で解決できそうです。
ちなみにPartition　By　のキーに複数項目を指定することはできるんでしょうか？
例えば、品番、サブ１、サブ２、サブ３ごとのデータで区切ってその中で昇順したい場面が出てきそうなので・・・
よろしくお願いします。

補足日時：2010/07/26 14:22

No.8 回答者： root139 回答日時： 2010/07/24 12:30

回答No.7 です。

WHERE句が間違っていました。ごめんなさい。

-------------------------------------------------------------------------
SELECT * FROM 対象テーブル a
WHERE
NOT EXISTS (SELECT 'x' FROM 対象テーブル b
WHERE a.店舗CD = b.店舗CD
AND (a.適用日付 < b.適用日付
OR (a.適用日付 = b.適用日付 AND a.ID < b.ID)));
-------------------------------------------------------------------------

この回答へのお礼

以前も同じような質問に回答していただき
、また今回も回答ありがとうございます。
無事問題は解決できました。
ありがとうございます。

お礼日時：2010/07/26 19:58

No.7 回答者： root139 回答日時： 2010/07/24 12:02

GROUP BY は使用しませんが、相関サブクエリを用いて下記の様に書くことが出来るかと。

同じ店舗で同じ適用日付に複数のIDが有る場合は若いIDが取得されます。
手元にORACLEが無いので未確認ですが。

-------------------------------------------------------------------------
SELECT * FROM 対象テーブル a
WHERE
NOT EXISTS (SELECT 'x' FROM 対象テーブル b
WHERE a.店舗CD = b.店舗CD AND a.適用日付 < b.適用日付 AND a.ID < b.ID);
-------------------------------------------------------------------------

「各店舗CDについて適用日付が最大のデータ」は以下の様に読み替えられます。
「『同じ店舗CDでより大きい適用日付の別のデータ』が無いデータ」
これをSQLで表現しています。

参考URL：http://codezine.jp/article/detail/1076?p=2

No.6 回答者： sekentetsu 回答日時： 2010/07/24 11:50

IDおよび適用日付を文字として結合してみてはどうでしょうか？

（データベースによって関数が異なるので出来るだけDBMSの種類をお教え頂ければと思います。）

MySQLの場合はCONCATで文字列結合なので以下のような感じです。

select 店舗CD, substring(A, 9,4) as ID from(
select 店舗CD, max(concat(適用日付,ID)) as A from テーブル
group by 店舗CD
) as T

結果としては
101, 20100901A003
102, 20100901B003
が返りますので、結合した文字の９文字目から４文字を取得すればIDが取れます。

※ORACLEの場合ROW_NUMBER()関数を利用すると一気に取ってくるのが可能です。

この回答へのお礼

ご回答ありがとうございます。
問題は無事解決できました。

お礼日時：2010/07/26 19:56

No.4 回答者： noname#116548 回答日時： 2010/07/24 07:26

データが大量の場合、DISTINCTは性能が悪くなります。

店舗CDと適用日付に対応するIDをどれでも良いのであれば「WHERE ROWNUM=1」とした方が良いのでは？
この様な問題は「SQL逆引き大全」などの本に色々記載されています。
購入されることをお勧めします。

この回答へのお礼

ご回答ありがとうございます。
そういう書籍を1冊買っておいておきたいと思います。
問題は無事解決できました。

お礼日時：2010/07/26 20:00

No.3 回答者： taka451213 回答日時： 2010/07/23 20:44

こんばんは。

例えば・・・、

SELECT DISTINCT MAX(ID) AS ID, 店舗CD, 適用日付
FROM
(SELECT ID, 店舗CD, 適用日付, MAX(適用日付) OVER(PARTITION BY 店舗CD) AS MAX_適用日付
FROM テーブル)
WHERE 適用日付 = MAX_適用日付
GROUP BY 店舗CD, 適用日付

とか・・・？。
今Oracleが手元にないので、ちょっと確認できませんが・・・。

この回答へのお礼

ご回答ありがとうございました。
教えていただいたSQLを書いて実行してみたのですが、
エラーがでてしまいました。
私の構文が間違っていたのかもしれません。
しかし、別の方法で問題は無事解決できました。
ありがとうございます。

お礼日時：2010/07/26 20:02

No.2 回答者： taka451213 回答日時： 2010/07/23 19:16

こんばんは。

いまいちよくわからないのと、Oracleのバージョンが不明なんですが・・・、

＞なおかつ欲をいえば、MAX(適用日付)のIDが複数存在する場合は、
＞１店舗CDにつき一つの適用日付を取得したいです。(この際、IDはなんでもいいです。)
これは無理じゃないですかね？
取得結果が何でもいいようなSQLは書けませんから・・・。
（MySQLなら可能ですが）

この辺りを精査して元データ、取得条件、希望の取得結果を補足して頂ければ可能です。

この回答への補足

ご回答ありがとうございます。
verは10.02です。
説明わかりにくくてすみません。
質問のテーブルデータでいえば、上から3番と最後の行のデータを2つ取得したいということです。
しかし、同じ適用日付と店舗CDでIDが違うデータが存在した場合、IDを一つに絞る方法がわからないということです。
説明になっているでしょうか？
かならず1店舗CDにつき一つの適用日付を取得したいんです。
そんな方法ないでしょうか？

補足日時：2010/07/23 20:09

No.1 回答者： noname#116548 回答日時： 2010/07/23 18:17

MAX（適用日付）を抽出した後店舗CDと適用日付でIDを取得するしかないんじゃない？