WHERE句内で「値＝最大値」を取得したい

Question

実施したいこと

WHERE句内で「値＝最大値」を取得したい
（調査する期間を絞った中での最大値に絞りたい）

##データ構造

create table user (id BIGINT(20), t  BIGINT(20));
insert into user values (20226, 1571632622304);

create table user_b (id BIGINT(20), t  BIGINT(20));
insert into user_b values (25726, 1569201884438);

実行しているSQL

select 
	a.id,
	from_unixtime(truncate(a.t,-3)/1000)  as a_time,
	from_unixtime(truncate(b.t,-3)/1000)  as b_time
    
from user a 

left outer join  user_b as b
on a.id =  b.id

where 

	b.time between  (unix_timestamp(DATE("2019-9-21")) - 9 * 3600) * 1000 and (unix_timestamp(DATE("2019-11-15")) - 9 * 3600) * 1000 -- 調査する期間を指定

	and b.time <= a.time -- a.time以前のデータを抽出
		
group by a.id

-- 結果

2019-10-21 04:37:02.0000 ←この期間だけに絞りたい
2019-09-23 01:24:44.0000

試したこと

where
b.time = (SELECT max(b.time) FROM user_b WHERE b.id = a.id)

→絞ることができず困っております。
ご教授いただければ幸いです。

Comments

[yambejp]

a,bテーブルの構造とサンプルデータをcreate table+insert形式で例示ください

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEされていなければなりません。テーブル内のデータはINSERTされていなければSELECTできません。ですから、質問にCREATE TABLEやINSERTを https://teratail.com/help/question-tips#questionTips3-7 の [コード] で提示した方が適切な回答が付き易いです。

[退会済みユーザー]

yambejp様、Orlofsky様

ご指摘いただきありがとうございます。
テーブル構造とサンプルデータを追記しました。
※初学者ゆえ、至らぬことあればご教授いただきたいです。

何卒よろしくお願いいたします。

[yambejp]

例示された範囲で「2019-10-21 04:37:02.0000 ←この期間だけに絞りたい」の
条件を満たすことはできないので回答困難です

[Orlofsky]

テーブル定義に日時情報をBIGINTで作るのは止めて、Date型やDatetime型を使っては？
10年くらい前だったか、SQLの設計なのに汎用機のCOBOLしか知らない大手SIerはDate型を使わないで指定日から何日前後、2つの日付間の日数、うるう年を求めるなどのDate型を使えば用意しないで済む関数を作られて工数を多くしていました。

[退会済みユーザー]

yambejp様

データを
2019-10-21 04:37:02.0000
2019-09-23 01:24:44.0000
→この２つに訂正しました。
ご確認いただけますでしょうか。

Orlofsky様
テーブル作成者が異なるため、変更することができません、、
このデータ型で対応できればと思っています。
アドバイスいただけますと幸いです。

[yambejp]

a_timeは「2019-07-30 14:54:36.0000」しか存在しないので10-21(略)や9-23は
ヒットしないということなんですが・・・

※新しいsampleをみると「1970-01-19 13:33:20.0000」になりますね？
話は変わりますが、selectの最後のカンマを外してください（文法エラー）

[m.ts10806]

結局cast使わないならなぜ前の質問で私の回答をベストアンサーにしましたか？

[退会済みユーザー]

yambejp様

わかりづらく申し訳ございません。
1571600222 = 2019-10-21 04:37:02.0000
1569169484 = 2019-09-23 01:24:44.0000
→変更いたしました。

この認識であっておりますでしょうか？

[退会済みユーザー]

yambejp様

再度サンプルと、文法エラーを修正いたしました。
ご確認いただけますでしょうか？

[Orlofsky]

わたしなら、テーブル設計の担当者にシステムの開発や維持に時間も費用もかかりますよ、って紳士的に教えて導いてあげます。人生を楽しく過ごせる処世術です。

Answer 1

とりあえずまとめ

• 元データ

create table user (id BIGINT(20), t  BIGINT(20));
insert into user values (20226, 1571632622304);

create table user_b (id BIGINT(20), t  BIGINT(20));
insert into user_b values (25726, 1569201884438);

• 検索

select 
a.id,
from_unixtime(truncate(a.t,-3)/1000)  as a_time,
from_unixtime(truncate(b.t,-3)/1000)  as b_time
from user a 
left outer join  user_b as b
on a.id =  b.id

• 結果

id	a_time	b_time
20226	2019-10-21 13:37:02.0000	NULL

tblには最初から１つしかデータがないのでmax的な処理は不要です