Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Lightbulb Unanswered: sorting double linked list

    I'm looking for a way to sort a table by a double linked list. I would like to accomplish this in one sql statement instead of seeking through a recordset.

    Example table:

    mytable
    -------------------------------------------------------------------------
    mytable_id bigint
    mytable_something varchar
    mytable_prev bigint -- references mytable_id
    mytable_next bigint -- references mytable_id


    sql = SELECT mytable_id, mytable_something FROM mytable

    How could I order this by mytable_next or mytable_prev????

    Any ideas will be appreciated

    dbweasal
    Last edited by dbweasal; 04-06-03 at 20:42.

  2. #2
    Join Date
    Apr 2003
    Posts
    4

    Lightbulb

    If found this article for doing self joins: http://www.4guysfromrolla.com/webtec...120899-1.shtml

    It is kinda where I thought I should be going with this, but I still wouldn't have the records in the order I need them. I need to be able to tell sql where to start this self join. My start point would be the record with NULL as mytable_prev.


    It seems Oracle has the capabilities with 'start with' and 'connect by'

    Any Ideas on a solution for SQL SERVER???????

  3. #3
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: sorting double linked list

    Try this:

    select T1.id,T1.something,T2.something as Previous,T3.something as Next from mytable T1 left join mytable T2 on T1.prev=T2.id left join mytable T3 on T1.next=T3.id order by T2.something

    IONUT

    PS
    You need the "left joins" because otherwise your query wouldn't retrive data for the first and the last elements in the list.

    Eg: asumming you have a three rows data

    Id Something Prev Next
    1 elem_A NULL 2
    2 elem_B 1 3
    3 elem_C 2 NULL


    An query with "inner jons" will only retrieve this row:

    Id Something Prev Next
    2 elem_B elem_A elem_C


    Because for the first row wouldn't find a match in T2 and for the last row wouldn't find a match in T3

  4. #4
    Join Date
    Apr 2003
    Posts
    4

    Re: sorting double linked list

    Is still wouldn't have these in any order. I need them ordered from mytable_prev = NULL to mytable_next = null. I don't think there is a one query solution to this problem.

  5. #5
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: sorting double linked list

    Assuming you have the next table declared in your database:

    mytable
    ---------------------
    NodeId bigint
    Name varchar(50)
    PrevNode bigint
    NextNode bigint

    Create the following store proc in your database

    CREATE PROCEDURE OrderDoubleLinkList AS
    declare @OrdTab table(NodeId bigint,Pos bigint)
    declare @CrtNode bigint,@Level bigint,@Continue char(1)
    set @CrtNode=(select NodeId from mytable where mytable.PrevNode is null)
    if @CrtNode is not null
    BEGIN
    set @Level=1
    set @Continue='Y'
    while @Continue='Y'
    BEGIN
    insert into @OrdTab values(@CrtNode,@Level)
    set @Level=@Level+1
    set @CrtNode=(select NextNode from mytable where NodeId=@CrtNode)
    set @Continue=(case when @CrtNode is null then 'N' else 'Y' end)
    END
    select T1.NodeId,T1.Name,T2.Name as PrevName,T3.Name as NextName
    from mytable T1 left join mytable T2 on T1.PrevNode=T2.NodeId
    left join mytable T3 on T1.NextNode=T3.NodeId
    inner join @OrdTab T4 on T1.NodeId=T4.NodeId
    order by T4.Pos
    END
    else
    select NULL as NodeId,NULL as Name,NULL as PrevName,NULL as NextName
    GO


    Good luck,
    IONUT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •