I have a table with the structure:
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