Let's have an employee hierarchy given with the following table:
CREATE TABLE employee(
id serial PRIMARY KEY,
first_name varchar NOT NULL,
manager_id integer,
FOREIGN KEY(manager_id) REFERENCES employee(id)
);
INSERT INTO employee(first_name, manager_id)
VALUES('Arden', null),
('Oliver', null),
('Luisa', null),
('Amelia', null),
('Olivia', null),
('Lily', 2),
('Ava', 2),
('Isabella', 2),
('Charlie', 2),
('Beatrice', 3),
('Stephanie', 3),
('Emily', 3),
('Mila', 3),
('Isla', 4),
('Ashley', 4),
('James', 7),
('Jack', 7),
('William', 8),
('Harry', 8),
('Robin', 8);
For an employee, for instance with id = 20, we can find the highest level manager using query:
WITH RECURSIVE cte AS
(
SELECT
0 cnt, id, first_name, manager_id
FROM
employee
WHERE
id = 20
UNION ALL
SELECT
cnt+1, employee.id, employee.first_name, employee.manager_id
FROM
cte INNER JOIN employee ON cte.manager_id = employee.id
)SELECT * FROM cte WHERE cnt = (SELECT max(cnt) FROM cte);
But I need to obtain the entire list of employees --- highest_level_manager like the following:
employee | highest_level_manager
----------------------------------
Robin | Oliver
Harry | Oliver
William | Oliver
Jack | Oliver
James | Oliver
Ashley | Amelia
Isla | Amelia
Mila | Luisa
Emili | Luisa
Stephanie | Luisa
Beatrice | Luisa
Charlie | Oliver
Isabella | Oliver
Ava | Oliver
Lily | Oliver
Olivia | null
Amelia | null
Luisa | null
Oliver | null
Arden | null
Does anyone know how to do this?
Walk the tree for all employees. Reduce recursion to ids, add names in the final query:
with recursive cte as
(
select
id, manager_id, 0 as level
from
employee
union all
select
c.id, e.manager_id, level+ 1
from cte c
join employee e on c.manager_id = e.id and e.manager_id is not null
)
select
e.first_name as employee,
m.first_name as highest_level_manager
from (
select distinct on(id) *
from cte
order by id desc, level desc
) c
join employee e on c.id = e.id
left join employee m on c.manager_id = m.id
See live demo in Db<>fiddle.