radio3回答ポイント 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です。

よろしくお願いします。

• はてなアカウントでログイン
• 無料ユーザー登録

ベストアンサー

• b-wind2008/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;
  

  こんなかんじ？

  
  

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

  スター

  • radio3 2008/07/28 20:40:00

    b-wind様!!

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

    しかしながら、結果は

    ID 3

    ID 4

    ID 5

    ORDER BY id DESC;

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

    ORDER BY max(created_at);

    としてみたところ、

    ID 3 浅い

    しか取れませんでした。

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

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

    成功できませんでした。

その他の回答

• kn19672008/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;
  

  スター

  • radio3 2008/07/28 23:42:01

    取れました！

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

chuken_kenkou2008/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

スター

• radio3 2008/07/28 23:44:32

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

  #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

b-wind2008/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;

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

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

スター

• radio3 2008/07/28 23:54:13

  ありがとうございます。

  #1

  ORDER BY created_at DESC;

  とするだけでOKでした。

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

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

  #2

  ID 5

  ID 4

  ID 3

  ID 2

  ID 1

  まで取れました。

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

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

chuken_kenkou2008/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

スター

• radio3 2008/07/28 23:58:06

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

  その通りなのです。

  SQL正解でした。

  ありがとうございます。

• その他の回答を読む

この質問へのコメント

• radio32008/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
  ;
  
  
• kn19672008/07/28 21:24:36
  b-wind氏の回答の
  ORDER BY id;
  を
  ORDER BY created_at DESC;
  に変えるとお望みのものになります。
  
  私の回答はb-wind氏とは少し違う形で同じことを行っていますので
  よろしければ・・・。
  
• radio32008/07/29 00:14:36
  おっしゃるとおりでした。
  自分でごちゃごちゃやってるうちに混乱して
  ORDER BY max(created_at) DESC;
  とやってしまい、気がつきませんでした。
  
  何しろ
  SELECT created_at FROM pitch p2
  WHERE p1.name = p2.name
  の意図もさっぱり理解できず...
  
  kn1968さんのSQLはさらにちんぷんかんぷんで(^^;;
  
  皆さんありがとうございました。
  
• kn19672008/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 指定されたカラムを返す。
  というような流れだと考えれば理解しやすいでしょう。
  実際の内部動作を表しているのではなく、理解しやすいように書いています。
• radio32008/09/16 00:55:44
  kn1967様ありがとうございます!!
  大変くわしい解説をいただき、深々感謝です！
  ありがとうございますm(_ _)m
  
  

質問の情報

登録日時

2008-07-28 19:36:44

終了日時

2008-07-29 00:39:12

回答条件

1人5回まで

この質問のカテゴリ

• コンピュータ
• ウェブ制作

この質問に含まれるキーワード

人気の質問

• いつも大変お世話になっています。 アスペルガー精神3級独身会社員34歳男です。 最近心身共に疲れがもの凄…hayakushinene11日前
• https://hikky-mura.org/t/topic/2523/7 でsma200さんという方に戸塚ヨットスクールを勧められましたが、僕…hayakushinene12日前
• 親族（実家）の犬が無駄吠えをしています。朝方に一時間くらい吠えているらしい。昼にも一時間くらい吠え続…匿名質問者2215日前
• 度々すみません。 私は以前から大学入試・成績など教育関連のネガティブな夢を見ていましたが、ここ最近そ…hayakushinene153日前
• 学歴詐称ではなくて、貴族称号詐称について伺います。イボンヌの香りという映画で、ロシアの伯爵を詐称して…匿名質問者3日前

メニュー

• 質問一覧
• カテゴリ一覧
• 無料ユーザー登録
• 人力検索はてなトップ

PC版

• お問い合わせ
• ヘルプ
• お知らせ
• はてなトップ