group by 関数で表示するカラムを選ぶ方法

質問

いつもお世話になっております。
MySQLで、group by関数を使用した際の表示カラムをコントロールしたいのですが、やり方が分りません。
具体的には

id | e_mail | date
1 | t1@t.jp| 2005-05-21
2 | t1@t.jp| 2005-05-22
3 | t1@t.jp| 2005-05-23
4 | t1@t.jp| 2005-05-24
5 | t1@t.jp| 2005-05-25
6 | t1@t.jp| 2005-05-26
7 | t1@t.jp| 2005-05-27
8 | t1@t.jp| 2005-05-28

というデータがあります。
このデータをe_mailでグループ化した場合に、表示されるe_mailを好きなidのものに置き換えたいのです。
通常のSQL文
select id,e_mail,date from table_name group by e_mail
だと、どのidのt1@t.jpというアドレスが表示されるのかコントロールできません。
好きなidやdateのt1@t.jpというe_mailを抽出する方法をご教授頂けると大変助かります。
宜しくお願い致します。

回答No.8

chukenkenkou · ベストアンサー

代表的な書き方を忘れていました。

SQL-92で規定され、SQL Server以外の主要なRDBMSで実装されている「行値式（または行値構成子）」という書き方です。

＜e_mail毎に最新の行を得たい＞
select *
from t1
where (e_mail,date) in(select e_mail,max(date)
from t1
group by e_mail);

＜e_mail毎に最古の行を得たい＞
select *
from t1
where (e_mail,date) in(select e_mail,min(date)
from t1
group by e_mail);

#6回答のSQLでは、e_mail、dateしか取り出していませんでした。
それ以外の列も取り出せるようにしたい場合は、以下のように書けます。また、別名を再使用していたので、その部分も変更しました。

＜e_mail毎に最新の行を得たい＞
select a.*
from t1 as a,
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date<=y.date
group by x.e_mail,x.date) as b
where a.e_mail=b.e_mail and a.date=b.date and rank=1
;

＜e_mail毎に最古の行を得たい＞
select a.*
from t1 as a,
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date>=y.date
group by x.e_mail,x.date) as b
where a.e_mail=b.e_mail and a.date=b.date and rank=1
;

お礼

ありがとうございます！
いろんな副問合せの形があるんですね。
SQLは奥が深いですね。
本当に勉強になります！ありがとうございました！

回答No.7

chukenkenkou

#3、#6回答者です。

#6回答のSQLは、以下のような書き方もできます。

＜e_mail毎にdateが最新の行を得たい＞
select x.e_mail,x.date
from
t1 as x,
(select e_mail,max(date) as max_date
from t1
group by e_mail) as y
where x.e_mail=y.e_mail and x.date=y.max_date
;

＜＜e_mail毎にdateが最古の行を得たい＞
select x.e_mail,x.date
from
t1 as x,
(select e_mail,min(date) as min_date
from t1
group by e_mail) as y
where x.e_mail=y.e_mail and x.date=y.min_date
;

回答No.6

chukenkenkou

#3回答者です。

RDBMSにおいて、格納順の検索は保証されません。
仮にRDBMS側で、後方追加が原則であっても、前方の領域の行が削除された場合、再利用される場合もあります。
そのため、格納順を意識したい場合は、利用者側で通番やタイムスタンプで意識する必要があります。

＜e_mail毎に最新の行を得たい場合＞
select *
from
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date<=y.date
group by x.e_mail,x.date) as x
where rank=1
;

＜e_mail毎に最古の行を得たい場合＞
select *
from
(select x.e_mail,x.date,count(*) as rank
from t1 as x,t1 as y
where x.e_mail=y.e_mail and x.date>=y.date
group by x.e_mail,x.date) as x
where rank=1
;

回答No.5

mooboogie

ひょっとして。
「group byしたとき、最古のデータが選択されるので困る」（質問文のケース）
という話でしょうか。

下記にズラズラ書きましたが、出来るというだけで意味はないスクリプトです。
最新のdateでの抽出目的でgroup byするのは意味が無いです。
それは＃２、＃３さんが書いてあるとおりです。

以下が本題です。

group byしただけだと、データベースの格納順で該当カラムの先頭データが表示されるようです。
逆に言えば、group byする前に用途にあった並びに並び替えないといけない。
つまり、今回の場合はdateを降順にしておかないといけない。

バージョンが5.xであると副問い合わせができるので、降順ソートしておいたレコード群をgroup byしてあげる必要があります。

drop table t3038229;
create table t3038229 (email varchar(30), date varchar(10),comment varchar(30));
insert into t3038229 values
("t1@t.jp","2005-05-22","ｔ１のＤＢ格納先頭"),
("t1@t.jp","2005-05-25","あ"),
("t1@t.jp","2005-05-23","い"),
("t1@t.jp","2005-05-24","う"),
("t1@t.jp","2005-05-26","え"),
("t1@t.jp","2005-05-27","お"),
("t1@t.jp","2005-05-28","ｔ１の最新"),
("t2@t.jp","2005-05-24","ｔ２のＤＢ格納先頭"),
("t1@t.jp","2005-05-21","か"),
("t2@t.jp","2005-05-25","き"),
("t2@t.jp","2005-05-23","く"),
("t2@t.jp","2005-05-26","ｔ２の最新"),
("t3@t.jp","2005-05-25","ｔ３のＤＢ格納先頭"),
("t3@t.jp","2005-05-26","ｔ３の最新");
/* 最新date抽出スクリプト */
select email,date,comment
from (select email,date,comment from t3038229 order by date desc) as a
group by a.email;
/* group by しただけ */
select email,date,comment
from t3038229 group by email;

お礼

ありがとうございます！
副問い合わせという事ができるんですね。
今まで使った事がありませんでした。これは便利ですね！
副問合せを使用すれば、やりたいことが全て解決しました！
本当に助かりました！

回答No.4

mooboogie

そもそも「好きな」値を表示（抽出）させたいという要望を実現するのは、「where」です。
置き換えたい場合は、「replace」という関数が使えます。
select id,replace(e_mail,'@t.jp','@softbank.ne.jp'),date from table_name;

皆さんの仰る通り、group byを使ってやりたいことが見えないのでこのような回答になってしまいました。

補足

ありがとうございます。
すみません。私の説明が全然不足しておりました。
皆さんがおっしゃる通り、group関数でグループ化するなら、idやdateでコントロールする必要はないです。
私がやりたい事は、e_mailでグループ化した際にdateでソートし、最新のt1@t.jpというe_mailのレコードを抽出したいという事なのです。

回答No.3

chukenkenkou

標準SQLでは、「group by」指定時には、select文の選択リストには、「group by」で指定した列か、max、min、countなどの集合関数しか指定できないことになっています。

＜例＞
＜＜正しい例＞＞
select c1,max(c2),min(c3)
from t1
group by c1

＜＜誤った例＞＞
select c1,c2,c3
from t1
group by c1

殆どのRDBMSでは、グルー化列や、集合関数で指定していない列を、select文の選択リストで指定すると、文法エラーにしています。

MySQLでは、このことに関して、拡張仕様を持っています。
http://dev.mysql.com/doc/refman/4.1/ja/group-by-hidden-fields.html?ff=nopfpls

これは、

select c1,c2,c3
from t1
group by c1

としても、「文法エラーにはしない」という一方で、「c2やc3は、グループ化の結果、一意な値にならないなら、結果は保証しない」ということです。
利用者は、上記の前提条件を満たす場合にのみ、MySQLの拡張仕様を利用することができます。そうでないなら、この拡張仕様を利用すべきではありません。

回答No.2

yambejp

そもそもすきなidを選ぶ時点でgroup byするのは整合性がないかと。

select `id`,`e_mail`,`date`
from `table_name`
where `e_mail`='t1@t.jp'
and `id`='1'

で十分では？

回答No.1

himajin100000

私はSQL 系のソフトの使用経験無いんで的外れだったらごめん。

>どのidのt1@t.jpというアドレスが表示されるのかコントロールできません。

そのクエリで表示されるの？

一応これを見ると
http://www.atmarkit.co.jp/fnetwork/rensai/sql03/sql1.html
>「SELECT」句には、GROUP BYで指定した列と集計関数のみを指定することができます。
を満たさないとき
SQL Serverでエラーになっていて

以下と照らし合わせるとMySQLでも同じようなエラーが出るみたいなんだけど・・・
http://sqlzoo.net/howto/source/z.dir/err979/sqlserver
http://sqlzoo.net/howto/source/z.dir/err979/mysql