こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

カラムで括った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)毎の最高点のレコードを1件づつ抽出したいのですが
可能でしょうか?
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点のレコードを抽出するには・・・
という所で行き詰っています
よろしくお願いします

投稿日時 - 2006-03-22 22:37:27

QNo.2045581

すぐに回答ほしいです

質問者が選んだベストアンサー

#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`)
;

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

投稿日時 - 2006-03-23 23:27:07

お礼

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

投稿日時 - 2006-03-24 10:19:46

このQ&Aは役に立ちましたか?

7人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.7

#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`
;

投稿日時 - 2006-03-23 23:04:00

ANo.6

どういう結果が得たいのかが、具体的になっていません。
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も一意になる必要があり、一意にならない場合は、結果は保証されません。

投稿日時 - 2006-03-23 22:01:18

補足

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

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

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

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

投稿日時 - 2006-03-23 22:35:40

ANo.5

あぁと何度もすみません。良く見たらすごく無駄
がおおい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`;

投稿日時 - 2006-03-23 19:36:11

ANo.4

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

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

投稿日時 - 2006-03-23 18:26:19

お礼

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

とても勉強になりました

投稿日時 - 2006-03-23 18:45:26

ANo.3

ごめんなさい、前回記述ミスです。
最初にnameをuniqueにしてテンポラリをつくって
INSERTで点数の高い順に流し込みをします。
2番目以降のヒットはnameがuniqueなので無視されます。
なので3番目の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`;

投稿日時 - 2006-03-23 14:28:48

補足

やってみました
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;
なので、まず5番目のデータがinsertされます
次に6番目のデータがinsertされるはずですが、
nameが被るのでDuplicateを起こすのではないでしょうか?


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

投稿日時 - 2006-03-23 14:41:21

ANo.2

たぶん、サブクエリなんでしょうけど。
わたしは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`;

投稿日時 - 2006-03-23 02:01:33

お礼

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

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

投稿日時 - 2006-03-23 14:06:36

ANo.1

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

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

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

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

投稿日時 - 2006-03-23 00:18:45

補足

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

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

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

投稿日時 - 2006-03-23 14:17:39

お礼

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

最初のselect(サブクエリ)で
並び替えるだけ
(抽出件数は6件、ソートkeyは
1.name
2.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

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

投稿日時 - 2006-03-23 16:58:43

あなたにオススメの質問