<p>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.</p>

<pre><code>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 &lt;&gt; a.CustomerID
)
</code></pre>

<p>So if tblCustomer looks like this:  </p>

<pre><code>ParentID    CustID
5            5
1            8
5            4
4            1
</code></pre>

<p>The result I get from the code above is:  </p>

<pre><code>ParentID    CustID
4            1
5            4
5            5
</code></pre>

<p>What I want is just the last row of that result:  </p>

<pre><code>ParentID    CustID
5            5
</code></pre>

<p>How do I just return the last record generated in the CTE (which would be highest level CustID)?</p>

<p>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.</p>

[{'answer_id': 35425, 'author': 'Trevor Abell', 'author_id': 2916, 'author_profile': 'https://Stackoverflow.com/users/2916', 'pm_score': 1, 'selected': False, 'text': "<p>I'm not certain I fully understand the problem, but just to hack &amp; slash at it you could try:</p>\n\n<pre><code>SELECT TOP 1 FROM cteLevelOne ORDER BY CustID DESC\n</code></pre>\n\n<p>That assumes that the CustID is also in order as in the example, and not something like a GUID.</p>\n"}, {'answer_id': 35557, 'author': 'AlexCuse', 'author_id': 794, 'author_profile': 'https://Stackoverflow.com/users/794', 'pm_score': 3, 'selected': True, 'text': "<p>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:</p>\n\n<pre><code>DECLARE @LookupID int\n\n--Our test value\nSET @LookupID = 1;\n\nWITH cteLevelOne (ParentID, CustID, Depth) AS\n(\n        SELECT   a.ParentID, a.CustID, 1\n        FROM     tblCustomer AS a\n        WHERE    a.CustID = @LookupID\n    UNION ALL\n        SELECT   a.ParentID, a.CustID, c.Depth + 1\n        FROM     tblCustomer AS a\n        INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID \n        WHERE c.CustID &lt;&gt; a.CustID\n)\nselect * from CTELevelone where Depth = (select max(Depth) from CTELevelone)\n</code></pre>\n\n<p>or, adapting what trevor suggests, this could be used with the same CTE:  </p>\n\n<pre><code>select top 1 * from CTELevelone order by Depth desc\n</code></pre>\n\n<p>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.</p>\n"}, {'answer_id': 24691911, 'author': 'Naveen raj', 'author_id': 3828330, 'author_profile': 'https://Stackoverflow.com/users/3828330', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<p>Msg 530, Level 16, State 1, Line 15\nThe statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p>\n'}]
['https://Stackoverflow.com/questions/35320', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3677/']