Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
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)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today