こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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が複数存在する場合は、1店舗CDにつき一つの適用日付を取得したいです。(この際、IDはなんでもいいです。)

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

投稿日時 - 2010-07-23 17:39:43

QNo.6058846

すぐに回答ほしいです

質問者が選んだベストアンサー

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

投稿日時 - 2010-07-24 09:28:50

補足

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

投稿日時 - 2010-07-26 14:22:40

このQ&Aは役に立ちましたか?

8人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

回答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-24 12:30:49

お礼

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

投稿日時 - 2010-07-26 19:58:16

ANo.7

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

投稿日時 - 2010-07-24 12:02:16

ANo.6

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
が返りますので、結合した文字の9文字目から4文字を取得すればIDが取れます。

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

投稿日時 - 2010-07-24 11:50:56

お礼

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

投稿日時 - 2010-07-26 19:56:43

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

投稿日時 - 2010-07-24 07:26:58

お礼

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

投稿日時 - 2010-07-26 20:00:23

ANo.3

こんばんは。

例えば・・・、

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が手元にないので、ちょっと確認できませんが・・・。

投稿日時 - 2010-07-23 20:44:14

お礼

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

投稿日時 - 2010-07-26 20:02:26

ANo.2

こんばんは。

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

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

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

投稿日時 - 2010-07-23 19:16:38

補足

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

投稿日時 - 2010-07-23 20:09:05

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

投稿日時 - 2010-07-23 18:17:24

あなたにオススメの質問