You didn't tell us what the attribute you are looking for is, so the following assumes you are looking for the parent with the value 1 in a column named Status where the Status column cannot be NULL.
WITH my_cte (CustomerID, ParentID, Level)
AS
(
SELECT CustomerID, ParentID, 0 AS Level
FROM [dbo].MyTable
UNION ALL
SELECT cte.CustomerID, cte.ParentID, cte.Level + 1
FROM [dbo].MyTable e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID
WHERE Level = 0 OR cte.Status <> 1),
cteOrdered As
(Select CustomerID, ParentID,
Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)
Select CustomerID, ParentID
From cteOrdered
Where LevelDown = 1;
Tom
thank you Tom, i spent at least half-day to test this and figure out what is going on but couldn't figure out the issue. below is some sample data and your code. the query never finishes running. please help...
DECLARE @Temp TABLE (CustomerID INT, ParentID INT, Status INT)
INSERT INTO @Temp VALUES (17, 14298, 0)
INSERT INTO @Temp VALUES (20, 17, 0)
INSERT INTO @Temp VALUES (14298, 16475, 1);WITH my_cte (CustomerID, ParentID, Level, Status)
AS
(
SELECT CustomerID, ParentID, 0 AS Level, Status
FROM @Temp
UNION ALL
SELECT cte.CustomerID, cte.ParentID, cte.Level + 1, cte.Status
FROM @Temp e
INNER JOIN my_cte AS cte ON e.CustomerID = cte.ParentID
WHERE Level = 0 OR cte.Status <> 1),
cteOrdered As
(Select CustomerID, ParentID,
Row_Number() Over(Partition By CustomerID Order By Level Desc) As LevelDown
From my_cte)
Select CustomerID, ParentID
From cteOrdered
Where LevelDown = 1
OPTION ( MAXRECURSION 0 )
as per the above dataset, for customer 20 for example, the parent should be 14298 since the status of this member (14298) is 1
-
Edited by
SnowShine429
Wednesday, May 20, 2015 10:26 PM