Skip to content
Advertisement

Recursive CTE in Postgres

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
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement