So I've got this approach:

create table #ObjectRelations

(

Id varchar(20),

NextId varchar(20)

)

insert into #ObjectRelations values ('G', 'B')

insert into #ObjectRelations values ('A', 'B')

insert into #ObjectRelations values ('B', 'C')

insert into #ObjectRelations values ('B', 'X')

insert into #ObjectRelations values ('C', 'E')

insert into #ObjectRelations values ('C', 'D')

insert into #ObjectRelations values ('E', 'F')

insert into #ObjectRelations values ('D', 'F')

declare @startIds table

(

Id varchar(20) primary key

)

;WITH

Ids (Id) AS

(

SELECTId

FROM#ObjectRelations

),

NextIds (Id) AS

(

SELECTNextId

FROM#ObjectRelations

)

INSERT INTO @startIds

SELECT DISTINCT

Ids.Id

FROM

Ids

LEFT JOIN

NextIds on Ids.Id = NextIds.Id

WHERE

NextIds.Id IS NULL

;WITH Objects (Id, NextId) AS

( -- This is the 'Anchor' or starting point of the recursive query

SELECT rel.Id,

rel.NextId

FROM #ObjectRelations rel

WHERE rel.Id IN (SELECT Id FROM @startIds)

UNION ALL -- This is the recursive portion of the query

SELECT rel.Id,

rel.NextId

FROM #ObjectRelations rel

INNER JOIN Objects -- Note the reference to CTE table name (Recursive Join)

ON rel.Id = Objects.NextId

)

SELECT DISTINCT *

FROMObjects

drop table #ObjectRelations

It determines the starting objects (which are those who doesn't have a parent).

Is there a way to sort the last select to put first the starting objects?

Like

Id NextId

------------ --------------

A B

G B

B C

B X

C D

C E

D F

E F