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

Question

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

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はなんでもいいです。)

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

k_o_r_o_c_h_a_n · Accepted Answer

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

root139 · Answer

回答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)));
-------------------------------------------------------------------------

root139 · Answer

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

sekentetsu · Answer

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()関数を利用すると一気に取ってくるのが可能です。

noname#116548 · Answer

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

taka451213 · Answer

こんばんは。

例えば・・・、

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

taka451213 · Answer

こんばんは。

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

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

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

noname#116548 · Answer

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

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

select ID,店舗CD,適用日付

この回答への補足

回答No.7 です。

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

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

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

こんばんは。

こんばんは。

この回答への補足

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

似たような質問が見つかりました

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

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

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