This class of problems is hard for SQL.
It's even harder with your particular table. It's not properly normalized. There is no level indicator. And input IDs can be from mixed hierarchy levels.
Setup
You clarified in a later comment that every path is terminated with a row that has "Parent" IS NULL (root), even if sample data in the question suggest otherwise. That helps a bit.
I assume valid "EmployeePositionId" as input. And no loops in your tree or the CTE enters an endless loop.
If you don't have a level of hierarchy in the table, add it. It's a simple task. If you can't add it, create a VIEW or, preferably, a MATERIALIZED VIEW instead:
CREATE MATERIALIZED VIEW mv_tbl AS
WITH RECURSIVE cte AS (
SELECT *, 0 AS level
FROM tbl
WHERE "Parent" IS NULL
UNION ALL
SELECT t.*, c.level + 1
FROM cte c
JOIN tbl t ON t."Parent" = c."Subdivision"
)
TABLE cte;
These would be the perfect indices for the task:
CREATE UNIQUE INDEX mv_tbl_id_uni ON mv_tbl ("EmployeePositionId") INCLUDE ("Subdivision", "Parent", level);
CREATE INDEX mv_tbl_subdivision_idx ON mv_tbl ("Subdivision") INCLUDE ("Parent", level);
See:
Query
Pure SQL solution with recursive CTE, based on a table with level indicator (or the MV from above):
WITH RECURSIVE init AS (
SELECT "Subdivision", "Parent", level
FROM mv_tbl
WHERE "EmployeePositionId" IN (4719, 4720, 4721) -- input
)
, cte AS (
TABLE init
UNION
SELECT c."Parent", t."Parent", c.level - 1
FROM cte c
JOIN mv_tbl t ON t."Subdivision" = c."Parent" -- recursion terminated at "Parent" IS NULL
)
, agg AS (
SELECT level, min("Subdivision") AS "Subdivision", count(*) AS ct
FROM cte
GROUP BY level
)
SELECT "Subdivision"
FROM agg a
WHERE ct = 1 -- no other live branch
AND level < (SELECT max(level) FROM cte WHERE "Parent" IS NULL) IS NOT TRUE -- no earlier dead end
AND level <= (SELECT min(level) FROM init) -- include highest (least) level
ORDER BY level DESC -- pick earliest (greatest) qualifying level
LIMIT 1;
db<>fiddle here
Covers all possible input, works for any modern version of Postgres.
I added basic explanation in the code.
Related:
Legal, lower-case, unquoted identifiers make your life with Postgres easier. See:
