Introduction
This article shows how common table expressions (CTE) in SQL Server are naturally suited for navigating trees, such as finding its longest path, or diameter.
Recall that a tree is an undirected graph where unique paths exist between any two nodes (i.e. vertices). Any node may be selected as its top node, with its own collection of paths from it to its leaves. A popular algorithm for finding the longest path in any tree is the following:
Choose any node as its top node Find the longest path from it to its leaves Change the top node to the leaf