【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

ベストアンサー

id:b-wind No.1

b-wind回答回数3344ベストアンサー獲得回数4402008/07/28 20:10:32

ポイント10pt
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;

こんなかんじ?


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

id:radio3

b-wind様!!

素早いご回答ありがとうございます!!

しかしながら、結果は

ID 3

ID 4

ID 5

ORDER BY id DESC;

とすれば 5, 4, 3の順で取得できますが、ID順ではなく、日付の降順で並べ替えたいのです。

ORDER BY max(created_at);

としてみたところ、

ID 3 浅い

しか取れませんでした。

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

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

成功できませんでした。

2008/07/28 20:40:00

その他の回答(4件)

id:b-wind No.1

b-wind回答回数3344ベストアンサー獲得回数4402008/07/28 20:10:32ここでベストアンサー

ポイント10pt
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;

こんなかんじ?


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

id:radio3

b-wind様!!

素早いご回答ありがとうございます!!

しかしながら、結果は

ID 3

ID 4

ID 5

ORDER BY id DESC;

とすれば 5, 4, 3の順で取得できますが、ID順ではなく、日付の降順で並べ替えたいのです。

ORDER BY max(created_at);

としてみたところ、

ID 3 浅い

しか取れませんでした。

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

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

成功できませんでした。

2008/07/28 20:40:00
id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012008/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;
id:radio3

取れました!

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

2008/07/28 23:42:01
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542008/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
id:radio3

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

#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
id:b-wind No.4

b-wind回答回数3344ベストアンサー獲得回数4402008/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;

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

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

id:radio3

ありがとうございます。

#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
id:chuken_kenkou No.5

chuken_kenkou回答回数722ベストアンサー獲得回数542008/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
id:radio3

>created_at列の値は、必ずしも昇順(新しい日付順)で格納されないのでしょうか?

その通りなのです。

SQL正解でした。

ありがとうございます。

2008/07/28 23:58:06
  • id:radio3
    -- データベース作成
    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
    ;

  • id:kn1967
    b-wind氏の回答の
    ORDER BY id;

    ORDER BY created_at DESC;
    に変えるとお望みのものになります。

    私の回答はb-wind氏とは少し違う形で同じことを行っていますので
    よろしければ・・・。
  • id:radio3
    おっしゃるとおりでした。
    自分でごちゃごちゃやってるうちに混乱して
    ORDER BY max(created_at) DESC;
    とやってしまい、気がつきませんでした。

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

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

    皆さんありがとうございました。
  • id:kn1967
    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はエイリアス(別名)と呼ばれ、長いテーブル名を読みやすく短くする意味もあれば
    1つのSQL内で同じテーブルをあたかも別々のテーブルであるかの如く使う場合にも使います。

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

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

    今回の場合は処理の流れとしてはいずれも同じで
    (1)FROM で指定されているテーブルから1レコード読み取られる
    (2)WHERE で条件に合致するかを検討する。
    (3)最終レコードでなければ(1)に戻る
    (4)ORDER BY 並び順を決定する。
    (5)SELECT 指定されたカラムを返す。
    というような流れだと考えれば理解しやすいでしょう。
    実際の内部動作を表しているのではなく、理解しやすいように書いています。
  • id:radio3
    kn1967様ありがとうございます!!
    大変くわしい解説をいただき、深々感謝です!
    ありがとうございますm(_ _)m

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

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

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

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません