I have a table with the structure:
x
Employee_ID Employee_Name Manager_ID
And, for each employee, I need to show the top manager ID. I mean, if for example, I have the EmployeeID 2 whose manager is 3 and, therefore, the number 3 has the manager number 5, I would have to show:
Empoyee_ID--Top_Manager
2 5
I need to do this with a Recursive CTE in Postgres.
Advertisement
Answer
Something like (for the level 2 manager) :
WITH RECURSIVE T AS
(
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID, 1 AS MANAGER_LEVEL
FROM MyTable
UNION ALL
SELECT T.EMPLOYEE_ID, T.EMPLOYEE_NAME, E.MANAGER_ID, MANAGER_LEVEL + 1
FROM MyTable AS E
JOIN T ON T.MANAGER_ID = E.EMPLOYEE_ID
WHERE T.MANAGER_LEVEL = 1 --> limiting to level 2 - 1
)
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID
FROM T
WHERE MANAGER_LEVEL = 2 --> retrieving only level 2, not level 1 and 2