May 14, 2013 at 5:09 am
I am sorry if this has been answered before - I have seen and read many articles about row_number and cte but cannot get my head around this particular issue.
I have a series of items which may have predecessors or may not. predecessors may themselves have predecessors - hence a recursive cte would seem ideal. I want to have an ordered list where any items with predecessors get run in the correct order but not with the same sequence number as other items.
here is my example input data (tabs don't seem to work very well): -
RowIDTaskNamePredecessorrunorder
1Task1NULL1
2Task2NULL1
3Task3NULL1
4Task4Task21
5Task5Task31
6Task6Task91
7Task7Task51
8Task8Task51
9Task9Task101
10Task10NULL1
The table is called testschedule. The runorder is the column I want to fill in.
so here is my cte and the resulting select.
with Successors (runorder, predecessor, taskname, Level)
as
(
select ts.runorder, ts.predecessor, ts.taskname,
0 as Level
from testschedule ts
where predecessor is null
union all
select ts.runorder, ts.predecessor, ts.taskname,
Level+1
from testschedule ts
inner join Successors as s
on ts.predecessor = s.taskname
)
select * , row_number() over (order by taskname) desiredorder from successors
OUTPUT
runorderpredecessortasknameLeveldesiredorder
1NULLTask101
1NULLTask1002
1NULLTask203
1NULLTask304
1Task2Task415
1Task3Task516
1Task9Task627
1Task5Task728
1Task5Task829
1Task10Task9110
NOW maybe I am being dense or "can't see the wood for the trees" but what I want is to convert the "select * , row_number ..." to an update to put the correct "desiredorder" value into the runorder column on taskschedule.
Can someone help me please?
TIA
Kevin
May 14, 2013 at 7:05 am
TO add to my previous post - the order produced by the cte is not correct as it can be seen by inspection that item Task6 should run after item Task9 but it does not.
I can't see a way to get the correct order.
I have also found the update I need, I think -
update testschedule
set runorder = sc.ROWNUM
from testschedule
inner join
(
select taskname, ROW_NUMBER() over (order by taskname) as ROWNUM
from successors
) sc
on testschedule.taskname = sc.taskname
I hope someone can help me.
May 14, 2013 at 9:23 am
Hi and welcome to the forums. You have a challenge here because the standard recursive CTE will not work because you have multiple roots. This is often referred to as a forest. There may be some other ways to deal with this but I put together something that should work for you.
One thing we need to see around here is ddl and sample data in a consumable format. This provides two advantages. First, there is no ambiguity about what your datatypes and columns names are. Secondly, you will find a lot more people willing to help. Since this is your first time around here I put this together for you to show you what I mean.
if OBJECT_id('testschedule ') is not null
drop table testschedule
create table testschedule
(
RowID int,
TaskName varchar(10),
Predecessor varchar(10),
runorder int
)
go
insert testschedule
select 1, 'Task1', NULL, 1 union all
select 2, 'Task2', NULL, 1 union all
select 3, 'Task3', NULL, 1 union all
select 4, 'Task4', 'Task2', 1 union all
select 5, 'Task5', 'Task3', 1 union all
select 6, 'Task6', 'Task9', 1 union all
select 7, 'Task7', 'Task5', 1 union all
select 8, 'Task8', 'Task5', 1 union all
select 9, 'Task9', 'Task10', 1 union all
select 10, 'Task10', NULL, 1
Notice how now it is super easy to grab this and start to work on the issue instead of putting all this together.
Now to deal with your issue. Your rCTE was actually very close to what I used. In fact, all I did was add the RootTask, changed the where clause slightly and slightly changed the ROW_NUMBER. The big difference is that I moved is to an iTVF.
Here is the function:
if OBJECT_id('GetChildren ') is not null
drop function GetChildren
go
create function GetChildren
(
@TaskName varchar(10)
) returns table
return
(
with Successors
AS
(
SELECT ts.runorder,ts.predecessor,ts.taskname, ts.taskname as RootTask, 0 AS LEVEL
FROM testschedule ts
where TaskName = @TaskName
UNION ALL
SELECT ts.runorder,ts.predecessor,ts.taskname, s.RootTask, LEVEL + 1
FROM testschedule ts
INNER JOIN Successors AS s ON ts.predecessor = s.taskname
)
select *, ROW_NUMBER() over (Order by LEVEL) as RowNum from Successors
)
go
Sweet now we can get the tree for each of the root levels, all we need know is a way to get the whole forest in go. First, I used a cte to get all the root level items and then cross apply to the table function.
;with RootLevels as
(
select RowID, TaskName, Predecessor, runorder
from testschedule
where Predecessor is null
)
select *
from RootLevels r
cross apply dbo.GetChildren(r.TaskName) x
order by RootTask, RowNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 3:07 pm
Many thanks for the reply. I was able to understand more clearly what was happening and have adapted your solution to give me the exact answer I needed.
Also I will remember if future to make the test data more accessible.
Great work!
Thanks again.
Kevin:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply