Ordering a linked list-structure in an SQL or LINQ query?

Question

I have a database that looks something like this:

id uniqueidentifier NOT NULL
data nvarchar
nextid uniqueidentifier NULL

This is a linked list, since each nextid refers to an identifier in this table, except for the last, here next is NULL. I know the identifier of the first node.

I want to SELECT them all in the correct order, given the starting identifier.

Is there a way to do this in T-SQL (Edit: SQL 2008) or LINQ?

I know I can write code to do this manually in C #, just wondering if I can already request in that order?

Answer 1

, [SortOrder] - , . .

with cteList as
(
    select id, data, nextid, 1 as [SortOrder]
    from #TableTemp
    where id = 'E8ADAA52-54F8-4FE3-BE59-9852E52B33F5' --id of the 1st item

    union all

    select #TableTemp.id, #TableTemp.data, #TableTemp.nextid, (cteList.[SortOrder] + 1) as [SortOrder]
    from #TableTemp
    join cteList on #TableTemp.id = cteList.nextid
)
select * from cteList
order by [SortOrder] asc

Answer 2

SQL Server? 2005 , CTE.

with linked_list as (
  select
    id, data, nextid
  from
    table
  where
    id = @head
  union all
  select
    t.id, t.data, t.nextid
  from
    table t
    join linked_list ll on t.id = ll.nextid
)
select
  *
from
  linked_list