同一コード毎に最新の名称を取得するSQL

いつもお世話になります。

以下のデータ構造において、以下の条件にあてはまるレコードを
取得するにはどのようなSQLになりますでしょうか。

(1)同一のコード、区分において、有効期間開始日が最新のもの
(2)同一のコード、区分、有効期間開始日において、有効期間終了日が最新のもの

【テーブル】
※主キー：コード、区分、有効期間開始日、有効期間終了日
コード　区分　有効期間開始日　有効期間終了日　名前
10000　A01　19800101　99991231　名称１
10000　A01　20000101　99991231　名称２
20000　A01　19800101　19991231　名称３
30000　C01　19800101　19991231　名称４
30000　C01　19800101　99991231　名称５
30000　B01　20000101　99991231　名称６
40000　D01　19800101　99991231　名称７
40000　D01　20000101　20201231　名称８
↓
【取得結果】
10000　A01　20000101　99991231　名称２
20000　A01　19800101　19991231　名称３
30000　C01　19800101　99991231　名称５
30000　B01　20000101　99991231　名称６
40000　D01　20000101　20201231　名称８

ようするに、最新データの名前のデータを取得したいのですが、その最新の
定義が上記にあげたものになります。

連続投稿になってしまい申し訳ないのですが、どうしてもスマートな
SQLを思いつかず、皆様のお知恵をお借りしたいと思った次第です。

宜しくお願いします。

No.5 ベストアンサー 回答者： root139 回答日時： 2012/03/18 15:31

質問者さんはスルーされていますが、#1さんのSQLも十分スマートだと思いますが・・・。

#1さんは EXISTS 句の中身だけ書かれている様だったので、それ以外を補完してみました。

------------------------------------
SELECT * FROM テーブル T1 WHERE NOT EXISTS
(SELECT 1 FROM テーブル T2
WHERE T1.コード = T2.コード
AND T1.区分 = T2.区分
AND ( T1.有効期間開始日 < T2.有効期間開始日
OR T1.有効期間開始日 = T2.有効期間開始日
AND T1.有効期間終了日 < T2.有効期間終了日));
------------------------------------

参考URL：http://codezine.jp/article/detail/907

この回答へのお礼

おっしゃるとおりです。

自分がSQLを読むことができなかったため気づくことができませんでした。

( T1.有効期間開始日 < T2.有効期間開始日
OR T1.有効期間開始日 = T2.有効期間開始日
AND T1.有効期間終了日 < T2.有効期間終了日)

の部分を解釈することができていませんでした。

exits句の補足もありがとうございました。これがあったから気づくきっかけになりました。
このSQLも十分スマートですね。
ありがとうございました。

お礼日時：2012/03/18 18:36

No.4 回答者： jjon-com 回答日時： 2012/03/17 12:19

実行していません。

マニュアルを見ただけです。
http://msdn.microsoft.com/ja-jp/library/ms186734 …

select コード, 区分, 有効期間開始日, 有効期間終了日, 名前
from (
select コード, 区分, 有効期間開始日, 有効期間終了日, 名前, row_number() over(partition by コード, 区分 order by 有効期間開始日 desc, 有効期間終了日 desc) as グループ毎連番
)
where グループ毎連番 = 1;

No.3 回答者： Siegrune 回答日時： 2012/03/17 11:41

(1)同一のコード、区分において、有効期間開始日が最新のもの

(2)同一のコード、区分、有効期間開始日において、有効期間終了日が最新のもの
という書き方では、
【テーブル】
コード　区分　有効期間開始日　有効期間終了日　名前
30000　C01　19800101　19991231　名称４
30000　C01　19800102　99991231　名称５
30000　C01　20000101　20111231　名称６
というデータがあれば（別の理由から、ないと思うけど）、
「名称５」がでるのが正しいのか「名称６」がでるのが正しいのか不明なため
(1)同一のコード、区分において、有効期間開始日が最新のもの
(2)(1)が複数取得できる場合において、有効期間終了日が最新のもの
として書くと
select コード,区分,有効期間開始日,有効期間終了日,名前
from tableA T1
where
T1.有効期間開始日 =
(select max(T2.有効期間開始日) from tableA T2
where T1.コード = T2.コード and T1.区分 = T2.区分)
and
T1.有効期間終了日 =
(select max(T3.有効期間終了日) from tableA T3
where T1.コード = T3.コード and T1.区分 = T3.区分 and T1.有効期間開始日 = T3.有効期間開始日)

※一般には、上記の「名称５」がでるのが正しいのか「名称６」がでるのが正しいのか不明な状況にならないように対策をとっているため（以下のようになるようにしているはず）
単に同一のコード、区分において有効期間終了日が最大もデータを取得すればいいはずです。

コード　区分　有効期間開始日　有効期間終了日　名前
30000　C01　19800101　19991231　名称４
30000　C01　19800102　19991231　名称５
30000　C01　20000101　20111231　名称６
30000　C01　20120101　99991231　名称５

select コード,区分,有効期間開始日,有効期間終了日,名前
from tableA T1
where
T1.有効期間終了日 =
(select max(T2.有効期間終了日) from tableA T2
where T1.コード = T2.コード and T1.区分 = T2.区分)
といった感じです。
・・・対策がうまく取れていなければおかしなデータがでてきますが。

この回答へのお礼

回答ありがとうございます。

確かに、普通なら終了日にはかぶらないMAX値が入ると思いますし、開始日と終了日にはかぶらない期間が指定されると思います。
（その期間はその名称だった、というのを意味すると思われる）
期間を持つテーブルの場合、普通は開始日のみ主キーとなっていると思いますが、なぜか開始日と終了日が入っており、登録しようと思えば
質問に出したようなデータも登録できるわけで、万が一登録されていたとしても、それなりに動いてくれる作りにしておきたいのです。
データ登録不備だから２件データがとれて正常に動作しなかった、というのは避けたいのです。

お礼日時：2012/03/18 10:14

No.2 回答者： nharasawa 回答日時： 2012/03/17 10:33

SELECT * FROM テーブル T1

WHERE 有効期間開始日=(SELECT MAX(有効期間開始日)
FROM テーブル T2
WHERE T1.コード = T2.コード
AND T1.区分 = T2.区分)
AND 有効期間終了日=(SELECT MAX(有効期間終了日)
FROM テーブル T2
WHERE T1.コード = T2.コード
AND T1.区分 = T2.区分
AND T1.有効期間開始日=T2.有効期間開始日)
ORDER BY 1,3
です。

この回答へのお礼

先の質問に続き、回答ありがとうございます。

確認してみて意図したとおりに動作しました。
正直前回に引き続き、今回もどのようなSQLにすればスマートなのか思いつかなかったのですが、すごいと思います。
SQL作成において、どのようにアプローチしていますでしょうか。
もしよかったら、教えて頂けるとうれしいのですが。。。

宜しくお願いします。

お礼日時：2012/03/18 10:21

No.1 回答者： nora1962 回答日時： 2012/03/17 09:28

NULLが絡むとややこしいかもしれませんが、

( SELECT 1 FROM テーブル T2
WHERE T1.コード = T2.コード
AND T1.区分 = T2.区分
AND ( T1.有効期間開始日 < T2.有効期間開始日
OR T1.有効期間開始日 = T2.有効期間開始日
AND T1.有効期間終了日 < T2.有効期間終了日 ) );

この回答へのお礼

回答ありがとうございます。

自分のSQL読解力が弱かったために気づくことができませんでした。
スマートなSQLですね。どのようにしてこのようなSQLを組み立てているのでしょうか。
教えて頂けるとうれしいのですが・・・

お礼日時：2012/03/18 18:39