Postgres use recursive CTE to find the root node

Question

I have seen and read many posts on using a recursive CTE to get the hierarchy from a self-related table.

I want to get just the root node of an entry in my table. In other words, if I have a suburb called Brooklyn, I want to return North America from the hierarchy (bottom-up): Brooklyn -> New York City -> NY -> USA -> North America.

In fact, just the id of the row North America.

I am not sure where to put filter for Brooklyn and how to just get the root node.

Answer 1

with recursive dat as (
    select id,
           parent_id
      from Location_table
     where id = :Id_Brooklyn 
     --
     union distinct
     --
    select a.id,
           a.parent_id
      from Location_table as a
      join dat as b on b.parent_id = a.id
)
select id
  from dat
  where parent_id is null;