|
Home > Archive > MS SQL Server > October 2005 > sorting a linked list
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sorting a linked list
|
|
| Mathias Fritsch 2005-10-30, 8:23 pm |
| 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
| |
| Dan Guzman 2005-10-30, 8:23 pm |
| 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
,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
>
|
|
|
|
|