タイムスタンプ型の最新の値をmax()で取得できない

Question

update_dataカラムはtimestamp型でINSERT INTOされるたびに自動で付与されます。
例えばこの様な形で格納されています。2021-08-11 09:11:09
これの最新の値を持つレコードを取得しようと思った場合、こちらのmax()でfetchするとシンタックスエラー等を吐き出さずに空の配列が出力されます。

  $sql = "SELECT * FROM my_text
  WHERE category_1 = ? AND category_2 = ? AND status = 1 AND update_date = (SELECT MAX(update_date) FROM my_text)";

一方で、他のやり方としてORDER BY ASC LIMIT 1で指定するとupdate_dataカラムに正しく最新の日付を持つレコードが取得されます。

  $sql = "SELECT * FROM my_text
  WHERE category_1 = ? AND category_2 = ? AND status = 1 ORDER BY update_date ASC LIMIT 1";

max()でも最新日付を取得できると各種記事で確認はしているのですが、SQL文が誤っているのか、型をキャストしなくてはいけないのか、わからずご質問させていただきました。

よろしくお願い申し上げます。

追記：
命題が曖昧であったため、本文に追記させていただきます。以下の通りでございます。
「 WHERE category_1 = ? AND category_2 = ? AND status = 1」と言う条件を満たすものの中で、update_dateのカラムが最大（最新日付）のレコードを取得したい

Comments

[TetsujiMiwa]

update_dateの条件のみで取得を試みても取得できないですか？
SELECT * FROM my_text WHERE update_date = (SELECT MAX(update_date) FROM my_text)

[pegy]

コメントをいただきありがとうございます。
ご提示のupdate_dateカラムだけをmax()条件としたコードであれば、取得することができることを確認いたしました。
よろしくお願い申し上げます。

[TetsujiMiwa]

では除外したパラメータの方で問題が起こっていると思います。category_1の条件のみで、条件の範囲内の結果が得られるか？category_2のみではどうか？statusのみではどうか？をご確認ください。

Answer 1

SELECT * FROM my_text
WHERE category_1 = ? AND category_2 = ? AND status = 1 AND
  update_date = (SELECT MAX(update_date) FROM my_text)

SELECT MAX(update_date) FROM my_text は、「my_text の全体の中で最も新しい更新されたレコードの update_date が返ります。
このレコードが category_1 = ? AND category_2 = ? AND status=1 を満たしていなかったら、該当レコードなしになりますよね？

つまり update_date は、「上の3つの条件を満たす中で最も新しいレコード」を探さねばなりません。

Comments

[pegy]

コメントをいただきありがとうございます。
半分理解できたのですが少し挙動と整合しない様に思える点もございます。
私が提示したSQL文では、「全体の中でupdate_dateカラムが最大（新）のもの」→「その中でcategory_1 = ? AND category_2 = ? AND status=1を満たすもの 」があれば結果を返してくると言うご説明と理解しました。
そう言う意味では、今回update_dateカラムが最大（新)のレコードはたまたま、後者の3条件を満たしていたのですが、空の配列を返してきています。
この点が不思議なのですが、私の解釈に誤りがあればご指摘頂ければ幸いです。
また、適切に命題を示しておらず申し訳ございません。ishina_yum様のコメントにも記載しましたが、
「 WHERE category_1 = ? AND category_2 = ? AND status = 1」と言う条件を満たすものの中で、update_dateのカラムが最大（最新日付）のレコードを取得したいと言う点にございます。
よろしくお願い申し上げます。

[tacsheaven]

ちょっと違います。あの SQL だと「最も新しい更新時刻のレコードであって、category_1 と category_2 が指定された値で、status が 1 であるもの」を引っぱるので、どれか一つでも条件に合致しなければ空になります。レコードの状態が提示されていないので不明ですが、update_date が本当に最新のものでしたか？　タイムスタンプ型ですから、ミリ秒以下の単位で違いますよ？

[pegy]

コメントありがとうございます。
テスト中なので、レコードは一つしかないのですが、update_dateには
「2021-08-11 09:11:09」
が格納されています。
その様な意味では「本当に最新のものでしたか？」の問い合わせについては「YES」であると思うのですが、最新のものを探しに行っているので、少し違和感はあります。

SELECT * FROM my_text
WHERE category_1 = ? AND category_2 = ? AND status = 1 AND
ver = (SELECT MAX(ver) FROM my_text)
※verが数値型
の場合にはなぜか正しく期待するレコード（category_1 = ? AND category_2 = ? AND status = 1の条件に合致しておりかつverが最大のもの）が出力yされます。。。

[tacsheaven]

update_data カラムの定義（create table した時の）によるかも知れません。

[pegy]

ありがとうございます。
おそらく
WHERE category_1 = 1 AND category_2 = 1 AND status=1 update_date = (SELECT MAX(update_date) FROM my_text)
で、
category_1|category_2 |status|update_data |
1 | 1 | 1 | 2021-08-11 09:11:09
の様なデータの持ち方をすれば、理論上はレコードは返してくれることになると理解しております。（もちろん、望む形の条件とはなっていないため、不適切ではあります。
当初テストしていたレコードが削除されてしまったので、もしかしたら、WHERE category_1 = ? AND category_2 = ? AND status = 1でパラメーターが誤っていた可能性も否定はできないのですが、カラムの定義を確認してみます。

Answer 2

条件はMAX算出の際にも、レコード抽出の際にもそれぞれ必要です。
別々の条件とせず、以下の様に考えると分かり易くありませんか？

SELECT * FROM my_text
WHERE (category_1, category_2, status, update_date) in (  
        SELECT category_1, category_2, status, max(update_date)
        FROM my_text
        WHERE category_1 = ? AND category_2 = ? AND status = 1
        group by category_1, category_2, status
      )

別々な条件にするなら以下の様な記述ですね。

SELECT * FROM my_text
WHERE category_1 = ? AND category_2 = ? AND status = 1 
  AND update_date = (
        SELECT max(update_date) FROM my_text
        WHERE category_1 = ? AND category_2 = ? AND status = 1 
      )

SELECT * FROM my_text
WHERE category_1 = ? AND category_2 = ? AND status = 1 
  AND not exists (
        SELECT 1 FROM my_text t
        WHERE category_1 = ? AND category_2 = ? AND status = 1 
          and t.update_date > my_text.update_date
      )

Comments

[pegy]

ありがとうございます、まだ慣れていない group by句やnot existsなどリファレンスを見ながらですが、なんとなくイメージが湧きました。当初提示のsql文ですが
$sql=
"SELECT * FROM my_text
WHERE category_1 = ? AND category_2 = ? AND status = 1 AND update_date = (SELECT MAX(update_date) FROM my_text)";
これを平易な日本語に換言するのであれば、
「最新のupdate_dateをもち、かつcategory_1 = ? AND category_2 = ? AND status = 1の条件を満たすレコードを取得する」と理解しております。
そうした場合の問題としては、並列の条件である最新のupdate_dateがcategory_1 = ? AND category_2 = ? AND status = 1に合致しなければ何も返してくれないので、
「category_1 = ? AND category_2 = ? AND status = 1の条件を満たすレコードの中で最新のupdate_dateを取得する」と言う命題に合致しなくなると考えました。

また、ご説明でこの後者の命題に一致するためには、皆様からもご説明があった、max(update_date)においても同じ条件を設定する必要があると理解しております。
この様な整理であっていましたでしょうか・・・・？

[sazi]

> WHERE category_1 = ? AND category_2 = ? AND status = 1」と言う条件を満たすものの中で、update_dateのカラムが最大（最新日付）のレコードを取得したい

回答したのは上記の命題に対するものです。
整理するのは、机上であれこれするより、条件を色々変えてSQLを直接発行して確認するのが良いと思います。

[pegy]

ありがとうございます。
少し混乱気味であったのですが、一つずつ条件を変えて発行してみます！
よろしくお願い申し上げます。

Answer 3

update_date = (SELECT MAX(update_date) FROM my_text)という条件では、全てのレコードの中で最も新しいupdate_dateの行を取得することになります。

この中にも同じようにcategory_1 = ? AND category_2 = ? AND status = 1を加えれば良いと思います。

Comments

[pegy]

ありがとうございます。
$sql =
"SELECT * FROM my_text t1
WHERE category_1 = ? AND category_2 = ? AND status = 1
AND update_date = (SELECT MAX(update_date) FROM my_text WHERE t1.category_1 = ? AND t1.category_2 = ? AND t1.status = 1)";

で実装することができた様に思えるのですが、命題としては「 WHERE category_1 = ? AND category_2 = ? AND status = 1」と言う条件を満たすものの中で、update_dateのカラムが最大（最新日付）のものとなり、これに合致しているのか少し自信がないのですが、合っていますでしょうか？

また、あっているとしたらこれ以上スッキリSQL文を書く術があればアドバイスを頂けると嬉しいです。SQLに不慣れで申し訳ございません。よろしくお願い申し上げます。

[ishina_yum]

SQLの形としてはそれで問題ないです。（与えるパラメタをミスっていなければ）

すっきりしたSQLとなると、質問文にご自身で書いている↓ででょうね。
$sql = "SELECT * FROM my_text
WHERE category_1 = ? AND category_2 = ? AND status = 1 ORDER BY update_date ASC LIMIT 1";

なお、『最大値を条件に入れる』のと『並び替えて最大のものを取得する』のでは、一概にどちらが優れているとは言えるものではありません。

[pegy]

コメントありがとうございます。理解することできました。
データ量が増えたタイミングでクエリ速度等を検証してみようと思いますが、どちらが優れているとは速やかに言えるものではないのですね。少なくとも可読性においては、スッキリするORDER BY句の方が良い様に思えますが、他の要素も踏まえて検討してみます。
朝からお力添えをいただけたことに御礼を申し上げます。

[pegy]

失礼しました。ASCではなくDESCですね。