【SQL】先着1名様100pt 下記SQLどこを修正すれば良いでしょうか？

Question

radio3

32

32もっと見る

230pt

コンピュータウェブ制作

【SQL】先着1名様100pt 下記SQLどこを修正すれば良いでしょうか？

各投手の最後の試合のレコードを取得したいのですが、
下記SQLでは最初の試合のレコードが取れてしまいます。

* 500文字以内制限に引っかかったのでコメント欄にサンプルデータを書きます。

-- レコード挿入
INSERT INTO `pitch` VALUES(1, '2008-01-01', '岩熊', 'ソフトバンク', '●');
INSERT INTO `pitch` VALUES(2, '2008-02-02', '田中', 'ソフトバンク', '●');
INSERT INTO `pitch` VALUES(3, '2008-03-03', '浅い', '上層学院', '○');
INSERT INTO `pitch` VALUES(4, '2008-04-04', '田中', 'オリックス', '○');
INSERT INTO `pitch` VALUES(5, '2008-05-05', '岩熊', '値弁和歌山', '○');

-- これでは最初の試合が返される。
SELECT *
FROM `pitch`
GROUP BY `name`
ORDER BY max( `created_at` ) DESC
;

↑のSQLの結果は、
ID 1 (岩熊の最古)
ID 2 (田中の最古)
ID 3
が返されます。

ID 5 (岩熊の最新がほしい)
ID 4 (田中の最新がほしい)
ID 3
が返されるSQLはどう書けば良いのでしょうか？

データベースはMySQL 5です。

よろしくお願いします。

回答の条件

1人5回まで

登録：2008/07/28 19:36:44
終了：2008/07/29 00:39:12

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

No.2

kn196729153012008/07/28 20:33:21

100pt

SELECT *
FROM pichi AS T1
WHERE NOT EXISTS(SELECT * FROM pitch AS T2 WHERE T2.name = T1.name AND T2.created_at > T1.created_at)
ORDER BY created_at DESC;

取れました！

ありがとうございます!!

2008/07/28 23:42:01

No.3

chuken_kenkou722542008/07/28 21:13:14

10pt

こんな感じでは？

select
  *
 from `pitch` as x
 where id=(select max(id)
            from `pitch`
            where x.`name`=`name`
 order by id desc

すみません。構文エラーと出ました。

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

2008/07/28 23:44:32

No.4

b-wind33444402008/07/28 22:05:35

100pt

ID順ではなく、日付の降順で並べ替えたいのです。

見落としてました。

せっかくなのでちょっと趣向を変えて。

SELECT * FROM pitch p1
  WHERE created_at >= ANY ( 
    SELECT created_at FROM pitch p2
      WHERE p1.name = p2.name
      GROUP BY p2.name
  )
ORDER BY created_at DESC

MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.4.2.3 ANY、IN、SOME とともに使用したサブクエリ

これなら日付け型であることも問題ないはず。

サブクエリ(意味を理解できてません(TT;

単体実行もエラーとなってしまい、

外側のテーブルとくっつけてるから、単体実行は無理ですね。

ありがとうございます。

#1

ORDER BY created_at DESC;

とするだけでOKでした。

なぜmax()してしまったのか... すみません。

ありがとうございました。

#2

ID 5

ID 4

ID 3

ID 2

ID 1

まで取れました。

別例出していただいたおかげで少し理解が進みました！

...と言いたいのですが難しい(^^;;

2008/07/28 23:54:13

No.5

chuken_kenkou722542008/07/28 22:28:51

10pt

ID順ではなく、日付の降順で並べ替えたい

created_at列の値は、必ずしも昇順（新しい日付順）で格納されないのでしょうか？

select 
  *
 from pitch as x
 where created_at=(select max(created_at)
                    from pitch
                    where x.name=name)
 order by created_at desc

＞created_at列の値は、必ずしも昇順（新しい日付順）で格納されないのでしょうか？

その通りなのです。

SQL正解でした。

ありがとうございます。

2008/07/28 23:58:06

radio3 2008/07/28 19:37:17

-- データベース作成
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-- テーブル作成
DROP TABLE IF EXISTS `pitch`;
CREATE TABLE `test`.`pitch` (
`id` INT NOT NULL auto_increment,
`created_at` DATETIME NOT NULL ,
`name` VARCHAR( 10 ) NOT NULL ,
`vs` VARCHAR( 10 ) NOT NULL ,
`status` VARCHAR( 10 ) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
;
-- レコード挿入
INSERT INTO `pitch` VALUES(1, '2008-01-01', '岩熊', 'ソフトバンク', '●');
INSERT INTO `pitch` VALUES(2, '2008-02-02', '田中', 'ソフトバンク', '●');
INSERT INTO `pitch` VALUES(3, '2008-03-03', '浅い', '上層学院', '○');
INSERT INTO `pitch` VALUES(4, '2008-04-04', '田中', 'オリックス', '○');
INSERT INTO `pitch` VALUES(5, '2008-05-05', '岩熊', '値弁和歌山', '○');

-- これでは最初の試合が返される。
SELECT *
FROM `pitch`
GROUP BY `name`
ORDER BY max( `created_at` ) DESC
;
kn1967 2008/07/28 21:24:36

b-wind氏の回答の
ORDER BY id;
を
ORDER BY created_at DESC;
に変えるとお望みのものになります。

私の回答はb-wind氏とは少し違う形で同じことを行っていますので
よろしければ・・・。
radio3 2008/07/29 00:14:36

おっしゃるとおりでした。
自分でごちゃごちゃやってるうちに混乱して
ORDER BY max(created_at) DESC;
とやってしまい、気がつきませんでした。

何しろ
SELECT created_at FROM pitch p2
WHERE p1.name = p2.name
の意図もさっぱり理解できず...

kn1968さんのSQLはさらにちんぷんかんぷんで(^^;;

皆さんありがとうございました。
kn1967 2008/07/29 03:52:49

WHERE句にある NOT EXISTS は”該当するものがあれば対象外にする”という意味です。

以下はpitchの中で”名前が同じ”で”日付が新しいもの”が別レコードとして存在していれば
そのレコードは対象から外すということになります。
WHERE NOT EXISTS(SELECT * FROM pitch AS T2 WHERE T2.name = T1.name AND T2.created_at > T1.created_at)

T1やT2はエイリアス（別名）と呼ばれ、長いテーブル名を読みやすく短くする意味もあれば
１つのSQL内で同じテーブルをあたかも別々のテーブルであるかの如く使う場合にも使います。

pitch AS T1 と pitch AS T2 は元は同じpitchなのだけれども
同じものが二つあるものとして扱っている訳です。
ちなみにMySQLではASは省略できるのでpitch T1やpitch T2などの書き方でもＯＫです。
私は文法ミスをおかさないようにASを付けるのを習慣としているだけでMySQLを主として
お使いの方々は省略するのが通例のようです。

ANYも似たようなものですが特定のカラムの情報を抜き出して全て比較するという手順のため
単純に該当有無だけを対象とするEXISTSよりも柔軟に対応できる分、速度は落ちます。

今回の場合は処理の流れとしてはいずれも同じで
（１）FROM で指定されているテーブルから１レコード読み取られる
（２）WHERE で条件に合致するかを検討する。
（３）最終レコードでなければ（１）に戻る
（４）ORDER BY 並び順を決定する。
（５）SELECT 指定されたカラムを返す。
というような流れだと考えれば理解しやすいでしょう。
実際の内部動作を表しているのではなく、理解しやすいように書いています。
radio3 2008/09/16 00:55:44

kn1967様ありがとうございます!!
大変くわしい解説をいただき、深々感謝です！
ありがとうございますm(_ _)m

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

リクエスト送信済

回答リクエストを送信したユーザーはいません

b-wind · Accepted Answer · 2008-07-28T20:10:32+09:00

SELECT * FROM pitch p1
  WHERE created_at = ( 
    SELECT max(created_at) FROM pitch p2
      WHERE p1.name = p2.name
      GROUP BY p2.name
  )
ORDER BY id;

こんなかんじ？

日時同士での一致はできるかどうか試してないから文字列に変換しないといけないかもしれない。

b-wind · Accepted Answer · 2008-07-28T20:10:32+09:00

SELECT * FROM pitch p1
  WHERE created_at = ( 
    SELECT max(created_at) FROM pitch p2
      WHERE p1.name = p2.name
      GROUP BY p2.name
  )
ORDER BY id;

こんなかんじ？

日時同士での一致はできるかどうか試してないから文字列に変換しないといけないかもしれない。

【SQL】先着1名様100pt 下記SQLどこを修正すれば良いでしょうか？

ベストアンサー

b-wind33444402008/07/28 20:10:32

その他の回答（4件）

b-wind33444402008/07/28 20:10:32ここでベストアンサー

kn196729153012008/07/28 20:33:21

chuken_kenkou722542008/07/28 21:13:14

b-wind33444402008/07/28 22:05:35

chuken_kenkou722542008/07/28 22:28:51

コメント（5件)

この質問への反応（ブックマークコメント）