SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE AAA (
ID int,
OrderId nvarchar(20),
tel nvarchar(20));
 
INSERT INTO AAA
VALUES
(1,'20211130000001','0987000111'),
(2,'20211130000002','0987000111'),
(3,'20211130000003','0987000222'),
(3,'20211130000004','0987000111'),
(3,'20211130000005','0987000222');
  
SELECT *
FROM AAA


ID
OrderId
tel


1
20211130000001
0987000111

2
20211130000002
0987000111

3
20211130000003
0987000222

3
20211130000004
0987000111

3
20211130000005
0987000222


CREATE TABLE BBB (
ID int,
OrderId nvarchar(20),
memo nvarchar(20),
Xdate date);
 
INSERT INTO BBB
VALUES
(1,'20211130000001',N'資料1','2021/11/3'),
(2,'20211130000001',N'資料2','2021/11/8'),
(3,'20211130000003',N'資料3','2021/11/8'),
(4,'20211130000001',N'資料4','2021/11/15'),
(5,'20211130000002',N'資料4','2021/12/4'),
(6,'20211130000003',N'資料5','2021/12/9'),
(7,'20211130000003',N'資料6','2021/12/13'),
(8,'20211130000004',N'資料7','2021/12/13'),
(9,'20211130000004',N'資料8','2021/12/14');
  
SELECT *
FROM BBB


ID
OrderId
memo
Xdate


1
20211130000001
資料1
2021-11-03

2
20211130000001
資料2
2021-11-08

3
20211130000003
資料3
2021-11-08

4
20211130000001
資料4
2021-11-15

5
20211130000002
資料4
2021-12-04

6
20211130000003
資料5
2021-12-09

7
20211130000003
資料6
2021-12-13

8
20211130000004
資料7
2021-12-13

9
20211130000004
資料8
2021-12-14


SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY AAA.OrderId ORDER BY BBB.Xdate DESC) AS NO,
AAA.OrderId,AAA.tel,BBB.memo,BBB.Xdate
FROM AAA
INNER JOIN BBB ON BBB.OrderId = AAA.OrderId
WHERE AAA.tel = '0987000111'
) AS K
WHERE K.NO = 1


NO
OrderId
tel
memo
Xdate


1
20211130000001
0987000111
資料4
2021-11-15

1
20211130000002
0987000111
資料4
2021-12-04

1
20211130000004
0987000111
資料8
2021-12-14

ID	OrderId	tel
1	20211130000001	0987000111
2	20211130000002	0987000111
3	20211130000003	0987000222
3	20211130000004	0987000111
3	20211130000005	0987000222

ID	OrderId	memo	Xdate
1	20211130000001	資料1	2021-11-03
2	20211130000001	資料2	2021-11-08
3	20211130000003	資料3	2021-11-08
4	20211130000001	資料4	2021-11-15
5	20211130000002	資料4	2021-12-04
6	20211130000003	資料5	2021-12-09
7	20211130000003	資料6	2021-12-13
8	20211130000004	資料7	2021-12-13
9	20211130000004	資料8	2021-12-14