sorting a linked list

Hi,
I have some tables which entries the enduser can give a new sequence.
My current implementation is an extra column "OrderID" that keeps track
of the sequence. Endusers will do a lot of CRUD in these tables and its
a pain to keep this sequences up to date.
A linked list would be much more convenient. Is there any chance to
implement an "order by" clause for a linked list?
Here is my sample code I use:

CREATE TABLE #Categories (
[ID] [int] NOT NULL ,
[Name] [nvarchar] (15)   NOT NULL,
OrderID int not null ,
Predecessor int null,
Sucessor int null
)


INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucessor)
values (1,'cat1',1,null,3 )
INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucessor)
values (2,'cat2',3 ,3,4)
INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucessor)
values (3,'cat3',2 ,1,2)
INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucessor)
values (4,'cat4',4 ,2,null)


SELECT * FROM #Categories Order By OrderID

Drop table #Categories

It currently sorts by OrderID but I want to sort by the sequence as
stated by Predesessor and Sucessor.
Triggers and Cursors are not an option. New features of SQLServer 2005
would be ok.

regards
Mathias

A linked list?  This is a strange design for a relational database.  Try the
following SQL 2005 CTE:

WITH Categories(ID, Name, OrderID, Sucessor, Sequence) AS
(
SELECT ID, Name, OrderID, Sucessor, 0 AS Sequence
FROM #Categories
WHERE #Categories.Predecessor IS NULL
UNION ALL
SELECT #Categories.ID, #Categories.Name, #Categories.OrderID,
#Categories.Sucessor, Sequence + 1
FROM #Categories
JOIN Categories ON Categories.Sucessor = #Categories.ID
)
SELECT ID, Name, OrderID
FROM Categories
ORDER BY Sequence

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mathias Fritsch" <mathiasfritsch@gmx.de> wrote in message
news:1130706134.951873.9180@f14g2000cwb.googlegroups.com...
> Hi,
> I have some tables which entries the enduser can give a new sequence.
> My current implementation is an extra column "OrderID" that keeps track
> of the sequence. Endusers will do a lot of CRUD in these tables and its
> a pain to keep this sequences up to date.
> A linked list would be much more convenient. Is there any chance to
> implement an "order by" clause for a linked list?
> Here is my sample code I use:
>
> CREATE TABLE #Categories (
> [ID] [int] NOT NULL ,
> [Name] [nvarchar] (15)   NOT NULL,
> OrderID int not null ,
> Predecessor int null,
> Sucessor int null
> )
>
>
> INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucesso
r)
> values (1,'cat1',1,null,3 )
> INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucesso
r)
> values (2,'cat2',3 ,3,4)
> INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucesso
r)
> values (3,'cat3',2 ,1,2)
> INSERT into #Categories ([ID], [Name]  ,OrderID,Predecessor
,Sucesso
r)
> values (4,'cat4',4 ,2,null)
>
>
> SELECT * FROM #Categories Order By OrderID
>
> Drop table #Categories
>
> It currently sorts by OrderID but I want to sort by the sequence as
> stated by Predesessor and Sucessor.
> Triggers and Cursors are not an option. New features of SQLServer 2005
> would be ok.
>
> regards
> Mathias
>