自己結合時の結合条件について

Question

前提・実現したいこと

1つのテーブルにあるカラムの最大値を取得したいです。
最大値(最新日)を取得したいカラムは日付_1、日付_2で、その両方の値を取得したいのですが、結合条件を以下とした場合の書き方がいまいち分からず困っております。

取得したいテーブルはこちらです。
テーブル名：table_a

実施したいこと
①nameとA_numberに該当するそれぞれの日付_1と日付_2の最新日を取得したい。
②サブクエリにて結果を取得した後、他のカラム(id)が取得できていないためidも最終的に取得したいです。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| id | name | A_number | 日付_1 | 日付_2 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| 1 | カラム_1 | A1 | 2020-01-01 | 2020-01-05 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| 2 | カラム_1 | A1 | 2020-01-02 | 2020-01-01 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| 3 | カラム_1 | A1 | 2020-01-03 | 2020-01-01 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| 4 | カラム_1 | A1 | 2020-01-04 | 2020-01-01 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－

想定の結果(最終的に取得したい結果)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| id | name | A_number | 日付_1 | 日付_2 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| 1 | カラム_1 | A1 | 2020-01-01 | 2020-01-05 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
| 4 | カラム_1 | A1 | 2020-01-04 | 2020-01-01 |
－－－－－－－－－－－－－－－－－－－－－－－－－－－－

発生している問題

なにも取得できない

実施したSQL

SELECT 
id,
name,
A_number,
日付_1,
日付_2
FROM 
table_a AS table_1
INNER JOIN (SELECT
                name AS NAME,
                A_number AS B_number,
                MAX(日付_1) AS Date1_B,
                MAX(日付_2) AS Date2_B
            FROM
                table_a 
            GROUP BY
                name,A_number) AS table_2
ON
table_1.name = table_2.name
AND table_1.A_number = table_2.B_number
AND table_1.日付_1 = table_2.Date1_B
AND table_1.日付_2 = table_2.Date2_B

試したこと

結合条件を
table_1.name = table_2.name
AND table_1.A_number = table_2.B_number
AND table_1.日付_1 = table_2.Date1_B
または
table_1.name = table_2.name
AND table_1.A_number = table_2.B_number
AND table_1.日付_2 = table_2.Date2_B
した場合は、nameとA_numberに該当する日付_1または日付_2の最新日が取得できます。

また、サブクエリで結果を取得する際は日付_1、日付_2両方とも最新の日付を取得することができました。

補足情報（FW/ツールのバージョンなど）

SQLServerを使用しております。
SQLServer2017、SQL Server Developer
操作はWINDOWS10
SQLのバージョンは14.0.2027.2を使用しております。

初歩的な質問で申し訳ございません。
分かる方おりましたらご教示のほどよろしくお願いいたします。

Comments

[Orlofsky]

質問のテーブル定義とデータは CREATE TABLE文とINSERT文で、希望されるSELECT結果も含めて https://teratail.com/help/question-tips#questionTips3-7 の [コード] で提示できると動作確認し易いので、適切なコメントが付き易いです。

[sazi]

ID取得するとして、日付_1と日付_2の最大のIDが違っている場合はどうするのですか？

[ttt_xxx]

Orlofsky様

申し訳ございませんでした。
SELECT結果(想定の結果(最終的に取得したい結果))を記載いたしました。
アドバイスありがとうございます。

[ttt_xxx]

sazi様

最大IDは取得する予定ではなく、最大値が欲しいのは日付_1と日付_2なのです……。
ご質問の意図が誤っていたら申し訳ございません。

[Orlofsky]

CREATE TABLE文とINSERT文に修正してください、って書いたのを理解できませんか？

[sazi]

> idも最終的に取得したいです。
と質問にはありますが？
サブクエリーで出来ているのに、結合で行いたい理由は何ですか？

[sazi]

ひょっとして、日付１または日付２のうち大きい方での最大値って事ですか？

[ttt_xxx]

Orlofsky様
すみません、文にということはtableとして書くのではなくSQLを書いたほうがよいということでしょうか？
気が急いてしまい、結果のみ追記してしまいました。
申し訳ございません。
少々お待ちください。

[ttt_xxx]

sazi様
>>サブクエリーで出来ているのに、結合で行いたい理由は何ですか？
サブクエリで取得した結果とこのテーブルに含まれているほかのカラムを表示したかったため、結合にしておりました。
>>日付１または日付２のうち大きい方での最大値
いえ、日付１と日付２両方の最大値を取得したいのです……。

[Orlofsky]

SQLでSELECTを実行する為にはCREATE TABLEが実行されてテーブルが存在していなければなりません。SELECTを実行した時にテーブル内に存在するデータを取得される為には、既にデータがINSERTされていなければなりません。

[ttt_xxx]

Orlofsky様

申し訳ございません。
次また質問があったときは記載いただいたように詳しく記載しようかと思います。
ご教示いただきありがとうございました。

[ttt_xxx]

ベストアンサーは一番最初に詳しくご教示いただいた方にさせていただきました。
申し訳ございません。
言葉少なく、説明不足の中ご回答いただきました皆様ありがとうございました。

Answer 1

遅ればせながら参戦
そもそも論として
・「日付2で最大をとったときの日付1」
・「日付1で最大をとったときの日付2」
は完全にゴミデータだと思うんですが

name	A_number	日付_1	id_1	日付_2	id_2
カラム_1	A1	2020-01-04	4	2020-01-05	1

↑理想の出力はこれではだめなんですか？
最大値が1行に完結している場合は1行になったりするので上の理想は微妙な気がするんですが。

CREATE TABLE #table_a
	(
	TableID int NULL,
	xName nvarchar(50) NULL,
	A_Number nvarchar(50) NULL,
	日付_1 datetime NULL,
	日付_2 datetime NULL
	)  ON [PRIMARY]

insert into #table_a(TableID,xName,A_Number,日付_1,日付_2)
 select 1 , 'カラム_1', 'A1'          , '2020-01-01', '2020-01-05'
insert into #table_a(TableID,xName,A_Number,日付_1,日付_2)
 select 2 , 'カラム_1', 'A1'          , '2020-01-02', '2020-01-01'
insert into #table_a(TableID,xName,A_Number,日付_1,日付_2)
 select 3 , 'カラム_1', 'A1'          , '2020-01-03', '2020-01-01'
insert into #table_a(TableID,xName,A_Number,日付_1,日付_2)
 select 4 , 'カラム_1', 'A1'          , '2020-01-04', '2020-01-01'


-- ↑SQLの質問者はこの部分を提示してほしいマジで。
-- 回答率めっちゃ上がるよ


--最終形この形でよくない？
select X.xName,X.A_Number,M1.日付_1,M1.TableID as ID_1,M2.日付_2,M2.TableID as ID_2
from (
	select A.xName,A.A_Number
	from #table_a as A
	group by A.xName,A.A_Number
) as X
	outer apply (
		select top 1 M1.TableID,M1.日付_1
		from #table_a as M1
		where M1.xName=X.xName
			and M1.A_Number=X.A_Number
		order by M1.日付_1 desc
	) as M1
	outer apply (
		select top 1 M2.TableID,M2.日付_2
		from #table_a as M2
		where M2.xName=X.xName
			and M2.A_Number=X.A_Number
		order by M2.日付_2 desc
	) as M2

-- どうしてもっていうなら
select X.xName,X.A_Number,Z.*
from (
	select A.xName,A.A_Number
	from #table_a as A
	group by A.xName,A.A_Number
) as X
	outer apply (
		select top 1 M1.TableID,M1.日付_1
		from #table_a as M1
		where M1.xName=X.xName
			and M1.A_Number=X.A_Number
		order by M1.日付_1 desc
	) as M1
	outer apply (
		select top 1 M2.TableID,M2.日付_2
		from #table_a as M2
		where M2.xName=X.xName
			and M2.A_Number=X.A_Number
		order by M2.日付_2 desc
	) as M2
	cross apply (
		select M1.TableID as ID,M1.日付_1,null as 日付2
		union all
		select M2.TableID as ID,null,M2.日付_2
	) as Z
order by Z.ID

drop table #table_a

Comments

[ttt_xxx]

ご連絡遅くなりまして大変申し訳ございません。

これをみてハッとしました……！
そうですね、勉強不足でした。
こちらのほうがきれいな形ですね。

申し訳ございません。
次また質問があったときは記載いただいたように詳しく記載しようかと思います。
ご教示いただきありがとうございました。

[sousuke]

個人的に質問者の理想データをなにかに使う場合に、複数行または１行返却されたこのデータをみて「その行の日付１または日付２のどっちかまたは両方が最大である」という点までしかわからない点が致命的だと思います。

Answer 2

SQL関数のGreatest()が使えると記述はもっと楽ですが、残念ながらSqlServerでは提供されません。
項目が多くなるとそれなりの記述になりますが、2項目の比較なら単に条件比較で良いでしょう。

SELECT t1.*
FROM  table_a as t1
      inner join (
        select name, A_number
             , Max(case when 日付_1 > 日付_2 then 日付_1 else 日付_2 end) 最大日付
        from table_a
        group by name, A_number
      ) as t2
      ON    t1.name = t2.name
        AND t1.A_number = t2.a_number
        AND case when t1.日付_1 > t1.日付_2 then t1.日付_1 else t1.日付_2 end=t2.最大日付

追記

ちょっと先走って回答になったので、訂正

SELECT t1.*
FROM  table_a as t1
where 日付_1 = (select max(日付_1) from table_a where name=t1.name and A_number=t1.A_number)
   or 日付_2 = (select max(日付_2) from table_a where name=t1.name and A_number=t1.A_number)

Comments

[ttt_xxx]

ご連絡遅くなりまして大変申し訳ございません。

こ、これです！！！
無事に取得できました！！！
ありがとうございました。
次また質問があったときは詳しく記載しようかと思います。
ご教示いただきありがとうございました。

Answer 3

nameとA_numberでgorup byして日付1、２のmaxを取ればいいのでは？

Comments

[ttt_xxx]

ご回答いただきありがとうございます。
初歩的なことを申し上げてしまうのですが、JOINせずにということでしょうか？

[yambejp]

JOINは不要にみえます・・・
が、想定結果を見ましたが
カラム_1、A1の日付1は最新は2020-01-02ですよね？
これは日付2が2020-01-05のデータがあるので引きづられているのですか？

[ttt_xxx]

申し訳ございません。たとえが悪くテーブルのデータを変更いたしました。
現状のデータとで申し上げますと、日付1の最新は2020-01-04なので、日付2の2020-01-01は最新ではありませんが、レコードが一緒のため結果に含まれる。
逆の日付2の最新は2020-01-05なので日付1は最新ではありませんが、レコードが一緒のため含まれる、といったものを想定しております。

[yambejp]

あ、よく見たらid=3,4のデータ変更したのですね？
これはデータがバリバリに競合しそうで難しい命題だと思います
日付1や日付2が最新のデータが１個しかない前提ですが、
他のデータを見るかぎり一つとは限らないですよね。
nameやnumber似関係なく日付1,日付2のデータが最新のものを
もつものをそれぞれ抽出するということなら
以下のような感じになります

[yambejp]

select * from tbl
where 日付_1=(select max(日付_1) from tbl)
or 日付_2=(select max(日付_2) from tbl)

[ttt_xxx]

ご連絡遅くなりまして大変申し訳ございません。

無事に取得できました。
次また質問があったときは詳しく記載しようかと思います。
ご教示いただきありがとうございました。

Answer 4

要件の再検討が必要です。

サブクエリで取得される日付_1と日付_2の最新日付は、同一のレコードである保証がありません。
実際、例示されているデータでは異なっている為、結果が表示されておりません。

必要なレコード(id)を取得する為の条件を整理してください。
例えば、日付_1＞＝日付_2なら日付_1、そうでないなら日付_2と結合。
他には、日付_1の最新日付のレコードと日付_2の最新日付のレコードで、最大のidとか。

追記
追加仕様を「日付_1＞＝日付_2なら日付_1、そうでないなら日付_2と結合」と仮定。

AND table_1.日付_1 = table_2.Date1_B
AND table_1.日付_2 = table_2.Date2_B

AND ((table_2.Date1_B >= table_2.Date2_B AND table_1.日付_1 = table_2.Date1_B)
  OR (table_2.Date1_B < table_2.Date2_B AND table_1.日付_2 = table_2.Date2_B))

なお、テーブル構造が不明瞭なので、未検証です。

Comments

[ttt_xxx]

ご回答ありがとうございます。

AND table_1.日付_1 = table_2.Date1_B
AND table_1.日付_2 = table_2.Date2_B
>> 実際、例示されているデータでは異なっている為、結果が表示されておりません。
ここの部分ですよね……

取得したいレコード(SELECT結果)につきまして追記いたしました。
※想定の結果(最終的に取得したい結果)　こちらです。
もしお時間御座いましたらご確認よろしくお願いいたします。

[YT0014]

表示された「最終的に取得したい結果」から推測するに、例に挙げた「日付_1＞＝日付_2なら日付_1、そうでないなら日付_2と結合」ですね。
依頼欄のsaziさんのコメント「日付１または日付２のうち大きい方での最大値」が正解で、良いでしょうか？

[YT0014]

なお、結果だけ記載されても、仕様は、”推測”しかできません。プログラミングをされるのなら、仕様を、できるだけ正確に、網羅的に、提示できるように努力してください。
同時に、相手の提示した仕様を理解できるようにもなってください。

[YT0014]

yambejpさんの回答で気づきましたが、重複レコードがあった場合の仕様も検討する必要がありそうですね。
やはり、要件の再確認が必要かと。

[ttt_xxx]

ご連絡遅くなりまして大変申し訳ございません。

最大値両方取得したかったのです。
申し訳ございません。
次回質問する際はもう少しきちんとまとめてからご質問させていただきます。
ご教示いただきありがとうございました。