How to Sort a Linked List in SQL

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.

Sorting a linked list in Mysql

In MySQL 8.0:

with recursive my_node (id) as (
    select id from table_a where parent_id is null
    union all
    select a.id from my_node n join table_a a on n.id = a.parent_id
  )
select * from my_node;

Linked List in SQL

Store an integer column in your table called 'position'. Record a 0 for the first item in your list, a 1 for the second item, etc. Index that column in your database, and when you want to pull your values out, sort by that column.

 alter table linked_list add column position integer not null default 0;
 alter table linked_list add index position_index (position);
 select * from linked_list order by position;

To insert a value at index 3, modify the positions of rows 3 and above, and then insert:

 update linked_list set position = position + 1 where position >= 3;
 insert into linked_list (my_value, position) values ("new value", 3); 

Ordering a Linked List-Structure in a SQL or LINQ Query?

Not sure of [SortOrder] makes any difference as I don't have enough data to test it. It allows you to sort in both directions.

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

SQL - Linked List Table - Is there a better way to write this query?

You can use a recursive CTE:

with cte as (
      select nodeid, nodeid as oldestnodeid
      from nodes
      where previousnodeid is null
      union all
      select n.nodeid, cte.oldestnodeid
      from cte join
           nodes n
           on cte.nodeid = n.previousnodeid
     )
select *
from cte
order by nodeid;

Here is a db<>fiddle.

Although this is also "iterative", it is much better than the cursor-based approach. Basically, all three "oldest" nodes are handled in parallel. So the query recurses 5 times -- for the As -- rather iteratively looping through 9 rows.

How to sort a sql result based on values in previous row?

This is actually sorting a linked list:

WITH SortedList (Id, objectBefore , projectID, testName, Level)
AS
(
  SELECT Id, objectBefore , projectID, testName, 0 as Level
    FROM YourTable
   WHERE objectBefore = -1
  UNION ALL
  SELECT ll.Id, ll.objectBefore , ll.projectID, ll.testName, Level+1 as Level
    FROM YourTable ll
   INNER JOIN SortedList as s
      ON ll.objectBefore = s.Id
)

SELECT Id, objectBefore , projectID, testName
  FROM SortedList
 ORDER BY Level

You can find more details in this post

---

Related Topics