By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with recursive t as (
select v.*
from (values (1, 1), (2, 1), (3, 2), (4, 4)) v(employee, manager)
),
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
| employee | lev |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 0 |
| 4 | 0 |
SELECT 4