By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH EmployeeTable AS (
SELECT 1000000 AS Employee_ID, 1000001 AS SuperiorID UNION ALL
SELECT 1000001, 1000002 UNION ALL
SELECT 1000002, 1000003 UNION ALL
SELECT 1000003, 1000004 UNION ALL
SELECT 1000004, 1000005
),
cte(EmployeeID, SuperiorID, n) AS (
SELECT Employee_ID, SuperiorID, 1
FROM EmployeeTable
WHERE Employee_ID = 1000000
UNION ALL
SELECT e.Employee_ID, e.SuperiorID, n+1
FROM cte t
INNER JOIN EmployeeTable e
ON t.SuperiorID = e.Employee_ID
)
SELECT TOP 1 *
FROM cte
ORDER BY n DESC;
| EmployeeID | SuperiorID | n |
|---|---|---|
| 1000004 | 1000005 | 5 |