CODEWITHSUNDEEP
sqlpostgresqlrecursion
Thomas Price
Thomas Price

Reputation: 101

Query how many levels are below a parent in a hierarchy

I am working with employee data and I think I need to write a recursive query, but I'm only a little familiar with them. I'm struggling to find out how to write a query that identifies how many levels are below a manager.

The data looks like this:

    | employee    | manager    |
    |-------------+------------+
    |  employee1  |  employee1 |

    |  employee2  |  employee1 |

    |  employee3  |  employee1 |
    |  employee3  |  employee2 |

    |  employee4  |  employee1 |
    |  employee4  |  employee2 |
    |  employee4  |  employee3 |

    |  employee5  |  employee1 |

    |  employee6  |  employee1 |
    |  employee6  |  employee2 |
...

Essentially this portion is telling me:

Any ideas/tips on how to write this? Thanks in advance!

Upvotes: 0

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can use a recursive CTE:

with recursive cte as (
      select employee, employee as s, 0 as lev
      from t
      union all
      select cte.employee, t.employee, lev + 1
      from cte join
           t
           on t.manager = cte.s and t.manager <> t.employee
     )
select distinct on (employee) employee, lev
from cte
order by employee, lev desc;

Here is an example in db<>fiddle.

Upvotes: 3

Related Questions