How do you get the last record generated in a recursive CTE?

Question

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.

Answer 1

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.

Answer 2

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.