can a query be written to do this...

I have the following table defined: Not my design/idea and I can't change
it)
CREATE TABLE [dbo].[Task] (
[TaskID] [ROWIDENTIFIER] NOT NULL ,
[Name] [SHORTNAME] NULL ,
[Description] [SHORTDESCRIPTION] NULL ,
[PreviousTaskID] [ROWIDENTIFIER] NULL ,
[NextTaskID] [ROWIDENTIFIER] NULL ,
[ProcedureID] [ROWIDENTIFIER] NOT NULL ,
[IsActive] [WFBOOL] NULL
) ON [PRIMARY]
PreviousTaskID and NextTaskID form, what amounts to a linked list where
PreviousTaskID points to the TaskID that comes before the current task and
NextTaskID points to the TaskID of the task that follows the current task.
A PreviousTaskID equal to null signifies the first task in a list and a
NextTaskID equal to null signifies it is the last task in the list.
With all of that in mind: Is there any way to write a query that will return
a single set of rows ordered from first to last?
TIA
Brian WBW -
It seems unnecessary to have a Next and Previous so long as the chain is
always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways, here'
s
something that should get you started:
create table #Task (
TaskID int not null
, [Name] varchar(50) null
, PreviousTaskID int null
, NextTaskID int null
)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
'T1', null, 2)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
'T1', 1, 3)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
'T1', 2, 4)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
'T1', 3, 5)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
'T1', 4, 6)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
'T1', 5, null)
declare @.parent_level int
set @.parent_level = 0
declare @.hierarchy table (parent int, item int, [level] int)
insert into @.hierarchy (parent, item, [level])
select null, taskid, 0
from #Task
where previoustaskid is null
while 1 = 1
begin
insert into @.hierarchy(parent, item, [level])
select nexttaskid, taskid, @.parent_level + 1
from #Task
where previoustaskid in (select item from @.hierarchy where [level] =
@.parent_level)
if @.@.rowcount = 0
break
set @.parent_level = @.parent_level + 1
end
select t.TaskID, h.[level] as Ordering
from #Task t
join @.hierarchy h on t.TaskID = h.item
order by 2 asc|||Perfect!
muchos gracias!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:C3096324-4209-4868-80AB-FA4FA7DB9B97@.microsoft.com...
> BW -
> It seems unnecessary to have a Next and Previous so long as the chain is
> always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways,
here's
> something that should get you started:
> create table #Task (
> TaskID int not null
> , [Name] varchar(50) null
> , PreviousTaskID int null
> , NextTaskID int null
> )
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
> 'T1', null, 2)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
> 'T1', 1, 3)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
> 'T1', 2, 4)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
> 'T1', 3, 5)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
> 'T1', 4, 6)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
> 'T1', 5, null)
> declare @.parent_level int
> set @.parent_level = 0
> declare @.hierarchy table (parent int, item int, [level] int)
> insert into @.hierarchy (parent, item, [level])
> select null, taskid, 0
> from #Task
> where previoustaskid is null
> while 1 = 1
> begin
> insert into @.hierarchy(parent, item, [level])
> select nexttaskid, taskid, @.parent_level + 1
> from #Task
> where previoustaskid in (select item from @.hierarchy where [level] =
> @.parent_level)
> if @.@.rowcount = 0
> break
> set @.parent_level = @.parent_level + 1
> end
> select t.TaskID, h.[level] as Ordering
> from #Task t
> join @.hierarchy h on t.TaskID = h.item
> order by 2 asc
>