problem in logic for finding a Recursive Cte
-
hi,
hope everybody doing good,
here is my problem this is my data
DECLARE @Tbl AS TABLE (Mainid INT, Referenceid INT)
INSERT INTO @Tbl
VALUES
(1,1),(1,2),(1,3),
(2,1),(2,3),(3,3),
(3,2),(4,3),(4,4),
(5,1),(5,3),(6,5),
(6,6),(7,6),(7,7),
(8,6),(8,7),(9,4),
(9,9),(9,10),(9,12)
SELECT * FROM @Tbl
For a particular reference id i try to get an output of it's sibling in each Mainid group and also the it's relative siblings also
well if i gave any one value of the following set, then i want the entire set as output
1
2
3
4
9
10
12
(i.e) the reference id relate with one another in each mainid group
so far this is my try not success yet,
WITH base as(
SELECT t.Mainid, t.Referenceid, ROW_NUMBER() OVER ( ORDER BY t.mainid,t.referenceid) Rn FROM @tbl t
),rec AS(
SELECT t.Mainid, t.Referenceid, rn
FROM Base T
WHERE t.Referenceid = 1
UNION ALL
SELECT t.Mainid, t.Referenceid, t.rn
FROM Base T
INNER JOIN rec ON T.Referenceid =rec.Referenceid AND T.rn >rec.rn
)
SELECT mainid, referenceid FROM rec
any help will be helpfull to me
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (5/3/2014)
hi,hope everybody doing good,
here is my problem this is my data
The problem is in the data, each node can only have one parent!
😎
-
Yes I know it, that's why I try to cleansing it, do you have any idea how to do it using TSQL
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Eirikur Eiriksson (5/4/2014)
thava (5/3/2014)
hi,hope everybody doing good,
here is my problem this is my data
The problem is in the data, each node can only have one parent!
😎
For classic tree's, like ORG charts or BOM's, that might be true but there are "Nets" like roads between cities that can have multiple "parents" (not really parents but relations).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thava (5/4/2014)
Yes I know it, that's why I try to cleansing it, do you have any idea how to do it using TSQLYou haven't really identified what this "Net" is for. It may very well be that it was never meant to be a classic "Adjacency List" where each child ID is unique and the list is acyclic. It may be the totally wrong thing to do to "clean it".
What does this list of relational nodes actually represent?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank's jeff I got mad about this problem, I just want some hint from any one angle, that's why I posted like it, you are right it is a many to many relationship, Well,it may be a long story, in short, we are working on a trace matrix, where every document is related with multiple tables, and every table has mapped documents based on its position , we want to find the dependency of a documents based on the table, I am not able to post structure or the data right now, and I am feeling the table design is more normalized, now I am in out of my system, once I came in front of my system I will explain more clearly thanks again
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (5/4/2014)
Yes I know it, that's why I try to cleansing it, do you have any idea how to do it using TSQLI'm not certain that a recursive CTE is the right approach here.
Quick questions to start with:
How many edges/levels would you have from end to end?
Can a node/entry be an ancestor of itself?
Would Mainid == Referenceid denote a start/start/top level entry? (My guess is that there are many entry/start points).
😎
-
Mainid is not directly related with Referenceid but it is used to group the referenceid, if i give the parameter values as 2 i need the following result set
11
12
13
21
23
33
32
43
44
51
53
94
99
910
912
i will explain you now we achieve this, we need to find the groups where the value 2 lies so in our data
in mainid 1 and 3
i that group we have other reference id's so the result might be
11
12
13
33
32
now i have to do the same process again for the reference id 1 and 3 because these are siblings for the reference id 2
and the process is go on until i am not able find any records
os now for 1 the resultset will be
11
12
13
21
23
for 3 the result will be
11
12
13
21
23
33
32
43
44
because all the groups have the value 3
now form the result we have to find the result set for 4, it is
43
44
94
99
910
912
sin ce there is no result for the values 9 10 12 the loop ends here
if i give the parameter values as 5 or 6 or 7 then i want to return the following resultset
65
66
76
77
86
87
hope you got it to clarify me
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Sounds like the ol "Traveling Salesman" problem. It CAN be done using a recursive CTE (rCTE) with a "stop" in it. I'll see if I can find some old code for this but not tonight. It's 1:30AM and I've gotta take a nap before I get up for work tomorrow (today).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thanks a lot i am waiting for it, take a deep sleep and have a good health we need you very much dear
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
The problem or at least part of it is mixing positions with the nodes in a Net. To mitigate this, lets use only the position (Mainid) to build the hierarchy, add a direction directive to avoid circulars and then fetch the nodes
😎
DECLARE @Tbl AS TABLE (Mainid INT, Referenceid INT)INSERT INTO @Tbl (Mainid,Referenceid)
VALUES
(100,1),(100,2),(100,3),
(200,1),(200,3),(300,3),
(300,2),(400,3),(400,4),
(500,1),(500,3),(600,5),
(600,6),(700,6),(700,7),
(800,6),(800,7),(900,4),
(900,9),(900,10),(900,12);
DECLARE @REF_ID INT = 2;
;WITH BASE_GROUP AS
(
SELECT
X.P_Mainid
,X.C_Mainid
,x.DIRECTION
FROM
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY T1.Mainid,T2.Mainid
ORDER BY (SELECT NULL)
) AS MRID
,T1.Mainid AS P_Mainid
,T2.Mainid AS C_Mainid
,CASE
WHEN T1.Mainid > T2.Mainid THEN 1
ELSE 0
END AS DIRECTION
FROM @Tbl T1
INNER JOIN @Tbl T2
ON T1.Referenceid = T2.Referenceid
WHERE T1.Mainid <> T2.Mainid
) AS X WHERE X.MRID = 1
)
,RC_GROUP AS
(
SELECT
BG.P_Mainid
,BG.C_Mainid
,BG.DIRECTION
FROM @Tbl T1
INNER JOIN BASE_GROUP BG
ON T1.Mainid = BG.P_Mainid
WHERE T1.Referenceid = @REF_ID
UNION ALL
SELECT
BG.P_Mainid
,RG.C_Mainid
,BG.DIRECTION
FROM BASE_GROUP BG
INNER JOIN RC_GROUP RG
ON BG.C_Mainid = RG.P_Mainid
AND BG.DIRECTION = RG.DIRECTION
)
,ALL_GROUP AS
(
SELECT
Y.Mainid
FROM
(
SELECT
X.Mainid
,ROW_NUMBER() OVER
(
PARTITION BY X.Mainid
ORDER BY (SELECT NULL)
) AS XRID
FROM
(
SELECT
RG.P_Mainid AS Mainid
FROM RC_GROUP RG
UNION ALL
SELECT
RG.C_Mainid AS Mainid
FROM RC_GROUP RG
) AS X
) AS Y WHERE Y.XRID = 1
)
,RELATED_REFS AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY T1.Referenceid ORDER BY (SELECT NULL)) AS RRID
,T1.Referenceid
FROM @Tbl T1
INNER JOIN ALL_GROUP AG
ON T1.Mainid = AG.Mainid
)
SELECT
RR.Referenceid
FROM RELATED_REFS RR
WHERE RR.RRID = 1
Results (Referenceid = 2)
Referenceid-----------
1
2
3
4
9
10
12
(Referenceid = 5)
Referenceid-----------
5
6
7
(Referenceid = 1)
Referenceid-----------
1
2
3
4
9
10
12
(added more results)
-
Hi Every body,
After a long war, Eventually I manage a win, i think it will be a hack, no bother, might be a lower performance,
any how i get the answer
DECLARE @Tbl AS TABLE (Mainid INT, Referenceid INT)
INSERT INTO @Tbl
(
Mainid, Referenceid
)
VALUES
(
100, 1
), (100, 2), (100, 3),
(200, 1), (200, 3), (300, 3),
(300, 2), (400, 3), (400, 4),
(500, 1), (500, 3), (600, 5),
(600, 6), (700, 6), (700, 7),
(800, 6), (800, 7), (900, 4),
(900, 9), (900, 10), (900, 12);
WITH BaseGrp AS(
SELECT ROW_NUMBER()OVER(PARTITION BY t1.Mainid ORDER BY t1.referenceID) AS
RN, T1.Mainid AS MID, T1.Referenceid AS RID, t2.Mainid AS Mid1,
t2.Referenceid AS Rid2
FROM @Tbl T1
INNER JOIN @Tbl t2
ON t2.Mainid = T1.Mainid
),RefidGrp AS(
SELECT DISTINCT Rid, rid2
FROM BaseGrp
),
Res AS(
SELECT rg.Rid, Rg.Rid2, ','+ CAST (Rg.Rid2 AS VARCHAR(MAX)) AS List
FROM RefidGrp Rg
WHERE rid = 1
UNION ALL
SELECT rg.Rid AS Id, rr.Rid2,rg.List+','+CAST (rr.Rid2 AS VARCHAR(MAX))
FROM Res Rg
CROSS APPLY(
SELECT rid2, ROW_NUMBER() OVER (ORDER BY rid2) Rn
FROM RefidGrp Rgp
WHERE rgp.Rid = Rg.Rid2 AND rg.List NOT LIKE '%,'+ CAST (rgp.Rid2 AS VARCHAR)+'%'
)Rr
)
SELECT DISTINCT Rid2 FROM res
After this hack i learn a few thing that
1)how the Recursive works?
2)never lose hope, until you get the result
/**** Edited *****/
Thanks Eirikur
That sample data will give some new idea how to work it out
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply