[SQL] Postgres - SQL Recursivity to get the highest parent

I need some help to create an SQL query with recursivity (POSTGRES). Let's explain.

This is a part of my table named "employe".

id string
nom string
manager string (reference the column id)


and this is a snapshot this is a snapshot of table "employe"

ID NOM MANAGER
1 MARTIN
2 DURANT 1
3 MARQUET 2
4 BEN HARFA
5 CASTEL 4
6 CALISKAN
7 BISSON 6
8 VERRATI 7
9 BALUZO 8
10 ROUSSET

what I want is to extract for each "employe" the highest manager in the hierarchy.

Im trying to do it by using recursivity. This is what i try :

WITH RECURSIVE q AS
(
SELECT m
FROM employe m
WHERE id = '9'
UNION ALL
SELECT m
FROM q
JOIN employe m
ON m.id = q.manager
)
SELECT (m).*
FROM q
WHERE (m).manager IS NULL


But it's not working...

It's an example of what i try to do :

ID NOM HIGHEST_MANAGER
1 MARTIN
2 DURANT 1
3 MARQUET 1
4 BEN HARFA
5 CASTEL 4
6 CALISKAN
7 BISSON 6
8 VERRATI 6
9 BALUZO 6
10 ROUSSET

Anyone can help me ? Thanks in advance.

Continue reading...