How do you get the last record generated in a recursive CTE?
In the code below I am using a recursive CTE(Common Table Expression) in SQL Server 2005 to try and find the top level parent of a basic hierarchical structure. The rule of this hierarchy is that every CustID has a ParentID and if the CustID has no parent then the ParentID = CustID and it is the highest level.
DECLARE @LookupID int --Our test value SET @LookupID = 1 WITH cteLevelOne (ParentID, CustID) AS ( SELECT a.ParentID, a.CustID FROM tblCustomer AS a WHERE a.CustID = @LookupID UNION ALL SELECT a.ParentID, a.CustID FROM tblCustomer AS a INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID WHERE c.CustID <> a.CustomerID )
So if tblCustomer looks like this:
ParentID CustID 5 5 1 8 5 4 4 1
The result I get from the code above is:
ParentID CustID 4 1 5 4 5 5
What I want is just the last row of that result:
ParentID CustID 5 5
How do I just return the last record generated in the CTE (which would be highest level CustID)?
Also note that there are multiple unrelated CustID hierarchies in this table so I can't just do a SELECT * FROM tblCustomer WHERE ParentID = CustID. I can't order by ParentID or CustID because the ID number is not related to where it is in the hierarchy.
If you just want want the highest recursion depth couldn't you do something like this?Then, when you actually query the CTE just look for the row with max(Depth)? Like so:
DECLARE @LookupID int --Our test value SET @LookupID = 1; WITH cteLevelOne (ParentID, CustID, Depth) AS ( SELECT a.ParentID, a.CustID, 1 FROM tblCustomer AS a WHERE a.CustID = @LookupID UNION ALL SELECT a.ParentID, a.CustID, c.Depth + 1 FROM tblCustomer AS a INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID WHERE c.CustID <> a.CustID ) select * from CTELevelone where Depth = (select max(Depth) from CTELevelone)
or, adapting what trevor suggests, this could be used with the same CTE:
select top 1 * from CTELevelone order by Depth desc
I don't think CustomerID was necessarily what you wanted to order by in the case you described, but I wasn't perfectly clear on the question either.
I'm not certain I fully understand the problem, but just to hack & slash at it you could try:
SELECT TOP 1 FROM cteLevelOne ORDER BY CustID DESC
That assumes that the CustID is also in order as in the example, and not something like a GUID.
First the cte will not be finished if any of the parent child are same. As it is a recursive CTE it has to be terminated. Having Parent and cust id same , the loop will not end.
Msg 530, Level 16, State 1, Line 15 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.