SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE temp (id int identity, address varchar(40), name varchar(40))
INSERT INTO temp(address, name) VALUES ('11 rd', 'aa');
INSERT INTO temp(address, name) VALUES ('11 rd', 'ab');
INSERT INTO temp(address, name) VALUES ('21 dr', 'ac');
INSERT INTO temp(address, name) VALUES ('21 dr', 'ab');
INSERT INTO temp(address, name) VALUES ('31 rd', 'ad');
INSERT INTO temp(address, name) VALUES ('21 dr', 'abb');
INSERT INTO temp(address, name) VALUES ('41 dr', 'abb');
7 rows affected

select * from temp


id
address
name


1
11 rd
aa

2
11 rd
ab

3
21 dr
ac

4
21 dr
ab

5
31 rd
ad

6
21 dr
abb

7
41 dr
abb


with edges as (
      select distinct t1.id as id1, t2.id as id2
      from temp t1 join
           temp t2
           on (t1.address = t2.address or t1.name = t2.name) and (t1.id <> t2.id)
     ),
     cte as (
      select id, id as next_id, convert(varchar(max), concat(',', id, ',')) as visited, 1 as lev
      from temp
      union all
      select cte.id, e.id2, concat(visited, e.id2, ','), lev + 1
      from cte join
           edges e
           on cte.next_id = e.id1
      where visited not like concat('%,', e.id2, ',%') and lev < 10
     )
select id, min(next_id)
from cte
group by id


id
(No column name)


1
1

2
1

3
1

4
1

5
5

6
1

7
1