SQL query left Join

Question

I have sample data like this

DECLARE @TBL TABLE(col1 VARCHAR(100),col2 varchar(100))

INSERT INTO @TBL VALUES ('server-1','11111')

INSERT INTO @TBL VALUES ('server-2','22222')

INSERT INTO @TBL VALUES ('server-3','33333')

INSERT INTO @TBL VALUES ('server-4','44444')

DECLARE @TBL2 TABLE(col1 VARCHAR(100),col2 varchar(100))

INSERT INTO @TBL2 VALUES ('server-1.mydomain.com','2 oct 2024')

INSERT INTO @TBL2 VALUES ('server-1.mydomain.com','1 oct 2024')

INSERT INTO @TBL2 VALUES ('server-1.mydomain.com','3 oct 2024')

INSERT INTO @TBL2 VALUES ('server-2.mydomain.com','1 oct 2024')

INSERT INTO @TBL2 VALUES ('server-3.mydomain.com','1 sept 2024')

INSERT INTO @TBL2 VALUES ('server-3.mydomain.com','3 oct 2024')

select * from @tbl

select * from @tbl2

and the result is

Now some one can help to to join both table with this criteria :

1. join tbl with tbl2 with col1 as key, and when joining to the tbl2 get only last record by col2 asc
2. any record with no data in the tbl2 will showing as null or empty.

So the final result is :

Answer 1

A possible solution:

;
with Q1 as
(
	select *, left(col1, charindex('.', col1)-1) s 
	from @tbl2
),
Q2 as
(
	select t1.col1, t1.col2, t2.col2 as d, row_number() over (partition by t2.s order by t2.col2 desc) as n
	from @tbl t1
	left join Q1 t2 on t1.col1 = t2.s
)
select col1, col2, d as col2
from Q2
where n = 1
order by col1

However, the type of @tbl2.col2 column should be changed to date, otherwise the queries are incorrect or difficult to made.