Get the last parent name from a table

Question

My objetive is get the top name and levelid of each level row.

Like the image below, In the top_level_name column at the first row should be filled with: "(103432,'282 ..."

In the second row, top_level_name should be: "Corrrea CV-130"

In 6: "146"

And so on...

How can I do that?

I have this table named "level"

And another named "rootlevel":

The level parentid column is the child levelid.

Answer 1

You can place your query as a table expression to pre-compute the joins, and then use a CASE clause to find the top level name.

For example:

select *,
  case when "level7.name" is not null then "level7.name"
       when "level6.name" is not null then "level6.name"
       when "level5.name" is not null then "level5.name"
       when "level4.name" is not null then "level4.name"
       when "level3.name" is not null then "level3.name"
       when "level2.name" is not null then "level2.name"
       when "level.name" is not null then "level.name"
  end as top_level_name
from (
  -- your select here
) x