Search code examples
sqlpostgresqlcommon-table-expressionhierarchical-datarecursive-query

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.


Solution

  • 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