SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table A(id int, name varchar(100))
create table B (id int, A_id int)
insert into A values(1, 'A')
1 rows affected

insert into A values(1, 'B')
1 rows affected

insert into A values(1, 'C')
1 rows affected

insert into A values(2, 'A')
1 rows affected

insert into A values(2, 'B')
1 rows affected

insert into A values(2, 'C')
1 rows affected

insert into B values(1, 1)
1 rows affected

insert into B values(2, 1)
1 rows affected

insert into B values(3, 1)
1 rows affected

insert into B values(4, 2)
1 rows affected

insert into B values(5, 2)
1 rows affected

insert into B values(6, 2)
1 rows affected

insert into B values(7, 3)
1 rows affected

select *
from ( select A.*
              , ROW_NUMBER() OVER(PARTITION BY id ORDER BY  id DESC) rn
       from A 
       where ID in (select top 2 A_id 
                    from B 
                    group by A_id
                    order by A_id asc)) Table_C
where rn <= 2


id
name
rn


1
A
1

1
B
2

2
A
1

2
B
2