カラムで括ったMAX値のselect

質問

Mysql5.0.18です
よろしくお願いします

テーブル：test_tblです
key：キー
name：生徒氏名
test_name：テストの名前
value：点数

下記のようなデータを用意しました
key　　name　　test_name　　value
1　　　　A　　　　test1　　　30
2　　　　A　　　　test2　　　50
3　　　　B　　　　test1　　　40
4　　　　B　　　　test2　　　80
5　　　　C　　　　test1　　　90
6　　　　C　　　　test2　　　90

下記のように
生徒（name）毎の最高点のレコードを１件づつ抽出したいのですが
可能でしょうか？
key　　name　　test_name　　value
2　　　　A　　　　test2　　　50
4　　　　B　　　　test2　　　80
5　　　　C　　　　test1　　　90

私は下記のsqlでチャレンジしてダメでしたTT
Select
test_tbl.key,
test_tbl.name,
test_tbl.test_name,
Max(test_tbl.value) as value
From
test_tbl
Group By
test_tbl.name
Order By
test_tbl.name Asc,
test_tbl.test_name Asc

結果は・・・
key　　name　　test_name　　value
1　　　　A　　　　test1　　　50
3　　　　B　　　　test1　　　80
5　　　　C　　　　test1　　　90
です・・・
Max関数を使えば、生徒のMAX点が出力されるのは
分かるのですが、そのMAX点のレコードを抽出するには・・・
という所で行き詰っています
よろしくお願いします

回答No.8

chukenkenkou · ベストアンサー

#6、#7回答者です。

keyの値と、name毎のtest_nameの関係が分かりません。name毎にtest_nameは昇順で、keyの値も昇順になっているのでしょうか？
それならば、#7の回等で問題ないと思います。
（`を打ったり、打たなかったりしている点はご容赦願います）

各name毎にtest_nameの昇順＝最小値を使って、keyを求める必要があるなら、以下のようなSQLになります。

select y.`key`,x.`name`,x.`test_name`,x.`value`
from (
select min(`test_name`) as `test_name`,`name`,`value` from `test_tbl`
where (`name`,`value`) in(
select `name`,max(`value`)
from `test_tbl`
group by `name`
)
group by `name`,`value`
) as x,
(select * from `test_tbl`) as y
where (x.`name`,x.`test_name`,x.`value`)=(y.`name`,y.`test_name`,y.`value`)
;

重複するデータから、最終的にユニークな値を求めなければならないというのは、検索条件が多くなってしまいます。

お礼

丁寧なご回答、ありがとうございます
max(value)の使い方がとても参考になりました
これで、問題なく解決できます
下記、No7.も含めて 参考にさせていただきます

回答No.7

chukenkenkou

#6回答者です。

MySQL 5.0以降とのことなので、サブクエリも制限なく使用できます。

nameでグループ化し、name毎のvalueの最大値を求め、
最大値を持つ行が複数ある場合は、最小のkey値の行を得る場合のSQL例です。

select x.`key`,x.`name`,y.`test_name`,x.`value`
from (
select min(`key`) as `key`,`name`,`value` from test_tbl
where (name,value) in(
select name,max(value)
from test_tbl
group by name
)
group by name,value
) as x,
(select `key`,test_name from test_tbl) as y
where x.`key`=y.`key`
;

回答No.6

chukenkenkou

どういう結果が得たいのかが、具体的になっていません。
nameでグループ化し、name毎のvalueの最大値を得たとして、同じvalue値があった場合、どのkeyの値を採用するのでしょうか？

MySQLでは、GROUP BY使用時の注意事項があります。

多くのRDBMSではGROUP BY使用時、SELECTで選択できるのは、GROUP BYで指定した列名か集合関数だけです。
ところが、MySQLではエラーとせず、グループ化することで、「一意になるなら指定してもよい。一意にならないなら結果は保証しない」とマニュアルにも記載されています。

SELECT C1,C2,C3
FROM T1
GROUP BY C1

上記のような書き方をした場合、C1をグループ化することで、C2、C3も一意になる必要があり、一意にならない場合は、結果は保証されません。

補足

要件が的確ではありませんでした、
申し訳ございません

同じvalueの場合はtest_nameの昇順
（この場合、test1）を採用します

＞一意になるなら指定してもよい。一意にならないなら結果は保証しない
うー、私もこれを心配していました
というのが、ANo.2の「お礼」で書いていますが
一度temporaryに格納しなおして（ソートしなおします）
その後nameでGroup化すれば　うまくいきました
ただ、この結果は保証されるのかな？・・・と疑問があったので
私のやりかた（ANo.2の「お礼」）はダメですね

とても参考になる回答ありがとうございました

回答No.5

yambejp

あぁと何度もすみません。良く見たらすごく無駄
がおおいSQLですしたね。何万件もあったら、
ほとんどがエラーになりますし。
普通やるならこんな感じの方が正しいのかなぁ・・・。

まず名前と最高点を拾っておく。
次にそれにあうIDとtest_nameをJOINで得る。
GROUP BY でtest1とtest2が一緒だったときの
重複をはじく。（test1とtest2はどちらが
ヒットするかは保証がありませんが）

CREATE TEMPORARY TABLE `Y` SELECT `name`,MAX(value) AS `value` FROM `test_tbl` GROUP BY `name` ;
SELECT `Z`.* FROM `Y` INNER JOIN `test_tbl` AS `Z` ON `Y`.`name`=`Z`.`name` AND `Y`.`value`=`Z`.`value` GROUP BY `Y`.`value`;

回答No.4

yambejp

手元にある、MySQL5の参考資料によると、
INSERTでエラーになるのをwarningで処理する書式
として、INSERT IGNOREがあるとあります。
プライマリーキーで"duplicate error"がでるときに
有効とのことですので、これですかねぇ？
まぁより堅牢なシステムになったというべきですか。

２番目のSQLを以下でお試しになってください
INSERT IGNORE INTO `X` SELECT * FROM `test_tbl` ORDER BY `value` DESC;

お礼

理解できました
今回テンポラリを使ったのは
抽出したい（各名の最高点のレコード）をテンポラリに格納してから
出力する・・・ということですね
テンポラリには3件しか格納しない！
なるほど！
ご丁寧な説明ありがとうございました！

とても勉強になりました

回答No.3

yambejp

ごめんなさい、前回記述ミスです。
最初にnameをuniqueにしてテンポラリをつくって
INSERTで点数の高い順に流し込みをします。
２番目以降のヒットはnameがuniqueなので無視されます。
なので３番目のSELECTにはGROUP BYはいりませんでした。
失礼しました。

CREATE TEMPORARY TABLE `X` (`key` VARCHAR(10),`name` VARCHAR(10) ,`test_name` VARCHAR(10) ,`value` INT ,UNIQUE( `name`));
INSERT INTO `X` SELECT * FROM `test_tbl` ORDER BY `value` DESC;
SELECT * FROM `X` ORDER BY `name`;

補足

やってみました
Inser文のところでDupります
-----
mysql> INSERT INTO `X` SELECT * FROM `test_tbl` ORDER BY `value` DESC;
ERROR 1062 (23000): Duplicate entry 'C' for key 1
------

＞INSERT INTO `X` SELECT * FROM `test_tbl` ORDER BY `value` DESC;
なので、まず５番目のデータがinsertされます
次に6番目のデータがinsertされるはずですが、
nameが被るのでDuplicateを起こすのではないでしょうか？

Mysqlバージョンの問題ですかね・・・

回答No.2

yambejp

たぶん、サブクエリなんでしょうけど。
わたしは3.23派なのであえてテンポラリで

CREATE TEMPORARY TABLE `X` (`keys` VARCHAR(10),`name` VARCHAR(10) ,`test_name` VARCHAR(10) ,`value` INT ,UNIQUE( `name`));
INSERT INTO `X` SELECT * FROM `test_tbl` ORDER BY `value` DESC;
SELECT * FROM `X` GROUP BY name ORDER BY `name`;

お礼

最高点で並び替えたものを、一旦テンポラリに格納して
それをGroupByですね！
なるほど、うまくいきました。
ありがとうございました！

UNIQUE( `name`)・・・これはDupるので
UNIQUE( `key`)ですね

回答No.1

driverII

サブクエリ（副問い合わせ）が使えませんかね。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

のような構文です。名前と最大点数でselectすればOKのようですが・・・

参考URL：

http://dev.mysql.com/doc/refman/4.1/ja/subqueries.html

お礼

いろいろやってるのですが、
解りません

最初のselect（サブクエリ）で
並び替えるだけ
（抽出件数は６件、ソートkeyは
１．name
２．value　Desc）

次のselect（本体）で　名前でGroup　By
ということでしょうか？

その場合、サブクエリと本体をつなげるWhere句を
keyとしたのですが、
デフォルトで並びがkey昇順となりますので
想定する結果が得られません・・・
select *
From
test_tbl
where
`key`
In
(
select `key`
From
test_tbl
Order By
test_tbl.name Asc,
test_tbl.value Desc
)
Group By
name

うーん、理解できないっす

補足

ごめんなさい、ちょっと理解できないです
副問い合わせは頻繁に使用していますので
構文は理解しているつもりです

＞名前と最大点でselectすればOK
との事ですが
一つ目のselectは「名前」で絞込み
二つ目のselectで「最大値」ということですかね・・・

うーん、なんとなく解る気がしてきました
もちょっとがんばってみます！