SQL Server 2019

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


CREATE TABLE t
    (ID1 int, ID2 int)
;
    
INSERT INTO t
    (ID1, ID2)
VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 3),
    (5, 4),
    (6, 5),
    (7, 5),
    (8, 6),
    (9, 7),
    (10, 6)
;


CREATE TABLE t2
    (ID2 int, ID3 int)
;
    
INSERT INTO t2
    (ID2, ID3)
VALUES
    (1, 1),
    (2, 1),
    (3, 2),
    (4, 3),
    (5, 2),
    (6, 4),
    (7, 5)
;
Msg 2812 Level 16 State 62 Line 1
Could not find stored procedure 'Date'.

select * 
from t;
select *
from  t2


ID1
ID2


1
1

2
2

3
3

4
3

5
4

6
5

7
5

8
6

9
7

10
6




ID2
ID3


1
1

2
1

3
2

4
3

5
2

6
4

7
5


with t1 as  (
select  ID1, ID2
from
(
select  *
       ,row_number() over(partition by ID2 order by ID1 desc) as rn
from    t 
) t
where rn = 1
            ),
t3 as       (
select  ID2, ID3
from
(
select  *
       ,row_number() over(partition by ID3 order by ID2 desc) as rn
from    t2
) t
where   rn = 1
            )

select   t1.ID1
        ,t1.ID2
        ,t3.ID3
from     t1 join t3 on t3.ID2 = t1.ID2
order by ID3


ID1
ID2
ID3


2
2
1

7
5
2

5
4
3

10
6
4

9
7
5