SQL Stored Procedure - Linked List

[Mythran]

I'm trying to take a set of records which all have a field called PreviousId as
well as ParentId.

PreviousId is the previous id of the row before the current row.
ParentId is the master id for the row.

Basically, I want to select all rows given a ParentId and return them in order so
the row that has PreviousId is first then the next row is the row that has it's
PreviousId set to the row's id that had PreviousId set to Null.

The full stored procedure should also allow a null ParentLinkId which returns ALL
links in order described above, but instead of returning all rows for a single
ParentId, it should select all rows and place them in order.

The table is laid out like:

RowId int identity(1,1)
....
ParentId int NULL
PreviousId int NULL

Example Data:

RowId ParentId PreviousId
1 2 4
2 NULL 3
3 NULL NULL
4 2 NULL
5 3 NULL


Basically, creating a two-tierd list of parent-child combination from one table.

Is this possible?

Thanks,

Mythran

 

[Patrice]

Likely. In the worst case you could use a temp table or a table variable as
it looks like you'll have to walk the list to return its item in the correct
order...

You could also look at possible improvements (such as numbering nodes when
updating) to be able to sort more easily the values...

Patrice

 

[tperovic]

Have you considered the nested set model instead of an adjacency list model?

See http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=86760

TP

 

[darren]

Yes it is possible.

Refer to "Versatile High Performance Hierarchies in SQL Server"
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm