GetAncestor (Database Engine)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Returns a hierarchyid representing the nth ancestor of this.

-- Transact-SQL syntax  
child.GetAncestor ( n )   

-- CLR syntax  
SqlHierarchyId GetAncestor ( int n )  

n
An int, representing the number of levels to go up in the hierarchy.

SQL Server return type:hierarchyid

CLR return type:SqlHierarchyId

Used to test whether each node in the output has the current node as an ancestor at the specified level.

If a number greater than GetLevel() is passed, NULL is returned.

If a negative number is passed, an exception is raised.

GetAncestor(1) returns the employees that have david0 as their immediate ancestor (their parent). The following example uses GetAncestor(1).

DECLARE @CurrentEmployee hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\david0'  
  
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeDemo  
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee ;  

GetAncestor(2) returns the employees that are two levels down in the hierarchy from the current node. These employees are the grandchildren of the current node. The following example uses GetAncestor(2).

DECLARE @CurrentEmployee hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\ken0'  
  
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeDemo  
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee ;  

To return the current node by using GetAncestor(0), execute the following code.

DECLARE @CurrentEmployee hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\david0'  
  
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeDemo  
WHERE OrgNode.GetAncestor(0) = @CurrentEmployee ;  

GetAncestor returns the selected level in the hierarchy even if a table isn't present. For example, the following code specifies a current employee and returns the hierarchyid of the ancestor of the current employee without reference to a table.

DECLARE @CurrentEmployee hierarchyid ;  
DECLARE @TargetEmployee hierarchyid ;  
SELECT @CurrentEmployee = '/2/3/1.2/5/3/' ;  
SELECT @TargetEmployee = @CurrentEmployee.GetAncestor(2) ;  
SELECT @TargetEmployee.ToString(), @TargetEmployee ;  

The following code snippet calls the GetAncestor() method.

this.GetAncestor(1)  

IsDescendantOf (Database Engine)
hierarchyid Data Type Method Reference
Hierarchical Data (SQL Server)
hierarchyid (Transact-SQL)