JOIN不是給你這樣用的。

LEFT JOIN
(
   SELECT TOP 1 * FROM B表
   WHERE OrderId = aa.OrderId --這裡有問題
   ORDER BY date DESC
)as bb

改成

LEFT JOIN
(
   SELECT TOP 1 * FROM B表   
)as bb ON bb.OrderId = aa.OrderId

連ORDER也沒用。

看你的用法似乎還不懂的如何利用JOIN。

SELECT aa.*, bb.*
FROM A表 aa,
     B表 bb
WHERE bb.date=(SELECT MAX(cc.date) From B表 cc WHERE bb.OrderId = cc.OrderId)
AND aa.OrderId=bb.OrderId
AND aa.Tel ='0987000111'

先湊一個應該有更好的做法

先取出單號最大日期，在做join

Declare @tempT1 Table (Id int ,OrderId varchar(100) ,tel varchar(20))
insert into @tempT1
select 1,'20211130000001','0987000111' union
select 2,'20211130000002','0987000111' union
select 3,'20211130000003','0987000222' union
select 4,'20211130000004','0987000111' union
select 5,'20211130000005','0987000222' 

Declare @tempT2 Table (Id int ,OrderId varchar(100) ,memo varchar(100) ,date Datetime)
insert into @tempT2
select 1,'20211130000001','資料1','2021/11/3' union
select 2,'20211130000001','資料2','2021/11/8' union
select 3,'20211130000003','資料3','2021/11/8' union
select 4,'20211130000001','資料4','2021/11/15' union  
select 5,'20211130000002','資料4','2021/12/4' union
select 6,'20211130000003','資料5','2021/12/9' union
select 7,'20211130000003','資料6','2021/12/13' union
select 8,'20211130000004','資料7','2021/12/13' union
select 9,'20211130000004','資料8','2021/12/14' 

declare @tel varchar(20) = '0987000111';

WITH temp2maxDate (OrderId ,date)
AS 
(  
	select OrderId,Max(date) from @tempT2 GROUP BY OrderId
)

select t1.OrderId,t1.tel,t2.memo,t2.date
from @tempT1 t1
join @tempT2 t2 on t1.OrderId = t2.OrderId
join temp2maxDate t3 on t1.OrderId = t3.OrderId and t2.date = t3.date
where t1.tel = @tel

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');

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 (
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

Demo

因為要還撈memo欄位資料所以不用Group By

還是可以用 Group By
如下

SELECT A.*,D.*
FROM A,(
SELECT B.* FROM B,
(
select OrderId, max(Xdate) as Xdate
from B
group by OrderId
) C
where C.OrderId=B.OrderId and C.Xdate=B.Xdate
) D
WHERE A.OrderId=D.OrderId

PostgreSQL 有一個特殊語法 distinct on 針對這類問題.但這不是標準的,在其他DB上無法使用.
提供大家參考.

create table it1206a (
  id int not null primary key
, orderid text unique
, tel text
);

insert into it1206a values
(1,'20211130000001','0987000111'),
(2,'20211130000002','0987000111'),
(3,'20211130000003','0987000222'),
(4,'20211130000004','0987000111'),
(5,'20211130000005','0987000222');

create table it1206b (
  id int not null primary key
, orderid text references it1206a(orderid)
, memo text
, xdate date
);

insert into it1206b values
(1,'20211130000001','資料1','2021/11/3'),
(2,'20211130000001','資料2','2021/11/8'),
(3,'20211130000003','資料3','2021/11/8'),
(4,'20211130000001','資料4','2021/11/15'),
(5,'20211130000002','資料4','2021/12/4'),
(6,'20211130000003','資料5','2021/12/9'),
(7,'20211130000003','資料6','2021/12/13'),
(8,'20211130000004','資料7','2021/12/13'),
(9,'20211130000004','資料8','2021/12/14');

select distinct on (orderid) orderid
     , memo
     , xdate
  from it1206b
 order by orderid, xdate desc;

    orderid     | memo  |   xdate    
----------------+-------+------------
 20211130000001 | 資料4 | 2021-11-15
 20211130000002 | 資料4 | 2021-12-04
 20211130000003 | 資料6 | 2021-12-13
 20211130000004 | 資料8 | 2021-12-14
(4 rows)


with t1 as (
select distinct on (orderid) orderid
     , memo
     , xdate
  from it1206b
 order by orderid, xdate desc
)
select t1.orderid
     , tel
     , memo
     , xdate
  from t1
  join it1206a
 using (orderid)
 order by 1;
 
    orderid     |    tel     | memo  |   xdate    
----------------+------------+-------+------------
 20211130000001 | 0987000111 | 資料4 | 2021-11-15
 20211130000002 | 0987000111 | 資料4 | 2021-12-04
 20211130000003 | 0987000222 | 資料6 | 2021-12-13
 20211130000004 | 0987000111 | 資料8 | 2021-12-14
(4 rows)

demo