Groupの中の条件に合致するデータを抜き出したい

質問

Groupの中の条件に合致するデータを抜き出したい

いくつかのテーブルからGroupでくくったデータを抜き出しました。
その中から条件に合致するデータに絞りたいのですが、
思った結果となってくれません。

DB:MySQL　5.1.50

以下のようなことをしたいと考えています。
[元のデータ]
ID　　　｜　日付　　　｜　担当　　　｜　金額
A-01　　｜2010-09-01　｜Angas　　　 ｜1000
A-01　　｜2010-08-31　｜Mayor　　　 ｜1500
A-01　　｜2010-08-25　｜Angas　　　 ｜2000
B-01　　｜2010-08-30　｜Roger　　　 ｜1350
B-01　　｜2010-09-01　｜Angas　　　 ｜2000
B-01　　｜2010-08-20　｜Roger　　　 ｜1350

[結果として欲しいデータ]
ID　　　｜　日付　　　｜　担当　　　｜　金額
A-01　　｜2010-09-01　｜Angas　　　 ｜1000
A-01　　｜2010-08-31　｜Mayor　　　 ｜1500
B-01　　｜2010-08-30　｜Roger　　　 ｜1350
B-01　　｜2010-09-01　｜Angas　　　 ｜2000

ちょっとデータ例が貧弱ですが・・・
IDと担当でグループ化した中で、日付が最も大きいレコードを抜き出したい。
というのが目的です。

どのようにSQLを書くと良いのでしょうか？
IDと担当でグループ化までは書けたのですが、
「グループのデータの中で日付で降順としたものの最新」というのを
どのように書いたものか悩んでいます。

PS．こういうSQLの書き方で悩んだ時に、どうやって情報を集めると
良い情報に出合えるかの助言も頂ければ助かります。

よろしくお願いします。

回答No.5

yamada_g

#3です。

>『同じテーブルを、id、担当で結びつけ、
>bにaの日付より大きなものが無い
>aのデータを抽出する。』
そうですね。
自分より大きい日付が存在しない = 自分が一番大きい日付 ということです。

#2さんの書かれている、
>order by した上位のものがgroup byで優先されるという保証はありません。
>またgroup byした場合、日付と金額の紐づけも確実ではありません。
ですが、group by というのは標準のSQLでは
集約項目を除いたselect項目を全て記述する必要があります。
MYSQLではそれを拡張し、group by にない項目もselectできるようになっています。
ただし、その項目の値がグループ内で一定でない場合は結果も一定にはなりません。
下記にそのことが書かれています。

http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

回答No.4

moousi

＃１です。

＃２さんのSQLは、要件が目に見える形で素晴らしいです。
＃３さんのSQLは、簡潔で素晴らしいです。

質問主さま、
＃１のSQLは、悪い例として扱い参考にしてください。

自己結合がぱっと頭に浮かぶようになりたいです＾＾；

お礼

moousiさん、たびたびありがとうございます。

同じ結果を求めるSQLって、幾筋もあるんですね。
それだけに特性を理解していないと、結果の出方も変わるということがわかりました。

> 質問主さま、
> ＃１のSQLは、悪い例として扱い参考にしてください。
moousiさんに回答をつけていただいたおかげで、
これだけの別の例も見ることができましたし、
おかげで、今回の例でいくと条件の甘さも新たに見つかりました。
たとえば、max（日付）が2レコード以上の場合など。
ありがとうございます。

回答No.3

yamada_g

not exists を使ってはどうでしょうか？
select *
from t1 a
where
　not exists(
　　select * from t1 b
　　where a.id = b.id
　　and a.担当 = b.担当
　　and a.日付 < b.日付)

お礼

yamada_gさん、回答ありがとうございます。
お礼が遅くなってすいません。

ぱっと見たときに解釈できませんでした。（＾＾；

『同じテーブルを、id、担当で結びつけ、
bにaの日付より大きなものが無い
aのデータを抽出する。』

日本語で書くと、こんな感じでしょうか？

existsって、普段は、ほとんど使ったことがありませんが、
使えそうですね。

ありがとうございます。

回答No.2

yambejp

#1さんのロジックはNGのはず。
order by した上位のものがgroup byで優先されるという保証はありません。
またgroup byした場合、日付と金額の紐づけも確実ではありません。

もしやるならこんな感じになります
(1)IDと担当で集計した最大の日付をだし
(2)IDと担当と日付が合致するデータを抽出する

select ID, 日付, 担当, 金額
from テーブル
WHERE (ID,日付,担当)IN (select ID,max(日付),担当 from テーブル group by ID,担当)

お礼

yambejpさん、回答ありがとうございます。

教えていただいたSQLにフムフムと感心しております。
とっても簡単なSQLなのかもしれませんが、
なかなか慣れないと見えてこなくて・・・

ただ、ここで疑問が湧いたのですが
> order by した上位のものがgroup byで優先されるという保証はありません。
> またgroup byした場合、日付と金額の紐づけも確実ではありません。
この辺の「仕様」というのは、どうやって見つけてこられるのでしょうか？

#1さんから教えていただいたSQLで例示の結果が得られたのですが、
逆に得られないような場合のデータを考え中です。

回答No.1

moousi

こんな感じになると思います。

select id ID, date(date)日付, tanto 担当, amount 金額
from
(select * from table1 order by date desc) a
group by id,tanto;

あらかじめ日付降順で並べ替えて置いたものをgroup byすると、日付が新しいものが選ばれます。

情報を集めるというか、SQL文を理解するのが結局早いのだ、と思います。
私も道半ばで、偉そうなことは言えない（＾＾；）
ttp://www.geocities.jp/mickindex/index.html
ttp://el.jibun.atmarkit.co.jp/g1sys/2009/02/sql-6be6.html

一応、テストで使ったテーブル構成を載せておきます。
create table table1
(id char(4), date datetime, tanto char(20), amount int);
insert into table1 values
('A-01','2010-09-01','Angas',1000),
('A-01','2010-08-31','Mayor',1500),
('A-01','2010-08-25','Angas',2000),
('B-01','2010-08-30','Roger',1350),
('B-01','2010-09-01','Angas',2000),
('B-01','2010-08-20','Roger',1350);

お礼

moousiさん、ご回答ありがとうございます。
お礼が遅くなって申し訳ございません。

実際に、教えていただいたSQLにて結果と同様のレコードが
抽出されたことを確認いたしました。

他につけていただいた回答も参考にさせていただきます。

参考URLもありがとうございます。
やはり一番の近道は、SQLの理解ですよね。