February 25, 2015 at 11:53 pm
ID ParentiD IsAutoCalculate Level
1 0 1 0
2 1 0 1
3 1 0 1
4 1 0 1
5 2 0 2
6 2 0 2
7 3 0 2
8 4 0 2
9 0 1 0
10 9 0 1
11 0 1 0
12 11 1 1
13 12 0 2
The above table shows a parent child relationship with the hierarchy shown in column level.
for each parent (IDs 1, 9, 11), I want the first child level where the column IsAutoCalculate = 0
so for parent Id 1, the rows to be returned is of level 1 as that is the first child row of this parent with IsAutoCalculate = 0. The rows with level 2 should not be returned
For parent id 3, the rows to be returned will be with level 2 as this is the first child row of this parent with IsAutoCalculate = 0
Thanks in advance
Warm Regards
Sridhar
February 26, 2015 at 6:03 pm
Looks like you need to use recursive CTE. But I don't understand your question and your output requirement
February 26, 2015 at 9:55 pm
IDParent IDFlagLevel
10 11
21 02
32 03
40 11
54 12
65 03
Let me explain it for you
1 is the parent of 2 and 2 is the parent of 3
4 is the parent of 5 and 5 is the parent of 6
for 1 & 4(these are the grandparents) I want the following output
The output should be the first child or grandchild which has the flag as 0
for 1, the child 2 and grandchild 3 have the flag as 0 However since 2 is at level 2, I want the output to reflect only child 2
for 4, the child 5 has flag as 1 and grandchild 6 as flag as 0. So only the grandchild 6 should be displayed as the output
so for all grandparents I want the first ancestor with flag as 0
Regards
Sridhar
February 26, 2015 at 10:42 pm
February 27, 2015 at 12:47 am
Try recursive cte as follows. I changed your input so that grandparent 1 has two different paths with IsAutoCalculate =(1...1,0) . You may need to tweak the query if only one path is required. I've assumed "first" means the least id within a parent.
with src as
(
select * from (values
(1,0,1,0),
--(2,1,0,1),
(2,1,1,1),
(3,1,0,1),
(4,1,0,1),
(5,2,0,2),
(6,2,0,2),
(7,3,0,2),
(8,4,0,2),
(9,0,1,0),
(10,9,0,1),
(11,0,1,0),
(12,11,1,1),
(13,12,0,2)) as T(ID, ParentiD, IsAutoCalculate, Level)
), rcte as (
select ID gId, ID, ParentiD, IsAutoCalculate, Level, cast(0 as bigint) as rn
from src
where ParentiD = 0
union all
select gId, src.ID, src.ParentiD, src.IsAutoCalculate, src.Level
, row_number() over (order by src.IsAutoCalculate, src.ID) as rn
from rcte
join src on rcte.Id = src.Parentid and rcte.IsAutoCalculate = 1
)
select * from rcte
where IsAutoCalculate = 0 and rn = 1
order by gId
February 28, 2015 at 6:49 am
Thank you very much.
It worked really well.
Thanks once again
March 2, 2015 at 5:29 pm
MaggieW (2/26/2015)
Looks like you need to use recursive CTE. But I don't understand your question and your output requirement
rCTEs are not the only way, and often aren't the fastest way:
The Performance of Traversing a SQL Hierarchy [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply