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.

From stackoverflow JohnMcCon

• 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.

  From Trevor Abell

• 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.

  From AlexCuse