How do I sort a linked list in sql?
In Oracle:
SELECT Id, ParentId, SomeData
FROM (
SELECT ll.*, level AS lvl
FROM LinkedList ll
START WITH
ParentID IS NULL
CONNECT BY
ParentId = PRIOR Id
)
ORDER BY
lvl
P. S. It's a bad practice to use NULL
as ParentID
, as it is not searchable by indices. Insert a surrogate root with id of 0
or -1
instead, and use START WITH ParentID = 0
.
I found a solution for SQLServer, but looks big and much less elegant than Quassnoi's
WITH SortedList (Id, ParentId, SomeData, Level)
AS
(
SELECT Id, ParentId, SomeData, 0 as Level
FROM LinkedList
WHERE ParentId IS NULL
UNION ALL
SELECT ll.Id, ll.ParentId, ll.SomeData, Level+1 as Level
FROM LinkedList ll
INNER JOIN SortedList as s
ON ll.ParentId = s.Id
)
SELECT Id, ParentId, SomeData
FROM SortedList
ORDER BY Level