SQLの構文の質問です。

質問

以下のテーブルがあります。
table
ID key code cls add_date
-- ---- ----- ---- ----------
1 0001 A01 0 2013/01/01
2 0001 A01 0 2013/01/15
3 0001 B01 0 2013/02/01
4 0002 C01 0 2013/01/20
5 0003 XYZ01 0 2013/01/10
6 0003 XYZ01 0 2013/01/20
：

このテーブルからkey、codeが重複したものを除いて、一意となる日付が最新のを
取り出したいのですがSQLをどのようにすればよいか教えてください。

結果：
2 0001 A01 0 2013/01/15
3 0001 B01 0 2013/02/01
4 0002 C01 0 2013/01/20
6 0003 XYZ01 0 2013/01/20

分析関数、相関関数を使うのでしょうか。
使わなくてもできるのでしょうか。
今後の為に、両方を教えてください。

回答No.4

yamada_g

NOT EXISTSを使う方法もあります。

SELECT id, key, code, cls, add_date
FROM table t1
WHERE NOT EXISTS (
SELECT *
FROM table t2
WHERE t1.key = t2.key
AND t1.code = t2.code
AND t1.add_date < t2.add_date
);

分析関数を使うなら、

SELECT id, key, code, cls, add_date
FROM (
SELECT id, key, code, cls, add_date, ROW_NUMBER() OVER(PARTITION BY key, code order by add_date desc) rnum
FROM table
)
WHERE rnum = 1;

といった感じでしょうか。

お礼

ありがとうございます。
分析関数の方もありがとうございます。

回答No.3

mitoneko

このような問題は、何段階かに分けて考えると、すっきりします。

　まず、同じグループの中で、最大の日付はなにか？という問いを解きます。
　select max(add_date) from table group by key, code

　となります。

　次に、各グループで、この最大の日付を持つレコードはどれですか？と続けます。先のＳＱＬが最大の値としてそのまま使えますから、

select max(t1.id), t1.key, t1.code, max(t1.add_date)
from dbo.table1 t1
where t1.add_date =
(select max(t2.add_date) from dbo.table1 t2 where t2.key=t1.key and t2.code=t1.code)
group by t1.key, t1.code

　ここで、group byは、外側のselect文に移行しています。その代わり、外側の見ているkey,codeが内側のkey,codeと一致するように内側のselect文にwhere句を付け加えます。これは、外側のselect文と内側のselect文は、同じグループを参照して集計する必要があるからです。
　外側のselect文のselect句で、idとadd_dateにmaxが付いている理由は、group byを使用したための構文上の逃げです。今回の質問が意味をなすデータが入っているとすれば、idもadd_dateも一意に決まるはずなので本来はいらないのですが、これをつけないと、システムに怒られます。group byを使用した時に、select句で使えるフィールドは、group by で指定したフィールドか集計項目だけと、構文規則で決まっているからです。ちなみに、一つのデータのmaxは、やはり元のデータですから、値としては影響がありません。minでもかまいません。値は同じですから。

　実は、このＳＱＬ、keyとcodeとmax(add_date)の組み合わせが一意であることを利用すると、もう少し簡単になることが解ります。外側で、group by key,code としていますが、一意であるならグループを組む価値がないわけです。
　そこで、group byをとってしまうと、

　select id, key, code , cls, add_date from table t1
where t1.add_date = (select max(t2.add_date) from table t2 where t1.key=t2.key and t1.code=t2.code)
order by t1.id

　余分なｍａｘも消えました。最後のorder by はおまけです。

　これで、できあがり。
　慣れてくると、分割しなくても、すっと出てくるようになります。
　ある項目の最大のデータを持つレコードに対して・・・・と言った場合は、同じ考え方でＳＱＬを組んでいけます。履歴を持ったテーブルを検索する時に、この類の問題は良く出ます。

お礼

ありがとうございます。
考え方、説明、アドバイス参考になりました。
先に回答頂いた方と同じ結果が得られました。

回答No.2

原沢 信道（@nharasawa）

SELECT ID,key,code,cls,add_date from "table" t1
where add_date=(select max(add_date) from "table" t2
where t2.key=t1.key and t2.code=t1.code) order by 1
です。

お礼

ありがとうございます。
確認してみます。

回答No.1

Siegrune

select key,code,max(add_date)
from table1
group by key,code
order by key,code
で、key,code別の日付の最大値が求められます。
(order by key,codeはkey,code順に表示させています。)

idとclsは、上記の情報から検索しないといけません。
例えば、
select * from table1
where (key,code,add_date) in
(select key,code,max(add_date)
from table1
group by key,code
)
order by key,code,id
というような感じ。
ただし、idでユニークと思われるので、
同一key,code,add_dateのidが複数あれば、複数でてきます。
（key,code,add_dateでユニークキーを定義してあればでてきませんが。）

お礼

説明ありがとうございます。
確認してみます。