Need sql query to find the lowest level of values in a hierarchy

Question

Table Organization(organizationId, parentId, name) Table Setting(id, organizationId, settingName, settingValue)

Here in |a,1| : a is settingName, 1 is settingValue

Organization A   |a,1|    |b,2|    |c,3|    |d,4|
Organization B            |b,5|
Organization C   |a,8|
Organization D                     |c,2|

A->B->C->D (hierarchy) Now get query of organization D should give me (a,8)(b,5)(c,2)(d,4)

My query :

with recursive cte as (
select
    *, 1 as level
from
    Organization
where
    organizationId = 3
union ALL 
select
    t.*, cte.level+1
from
    cte
join Organization t on
    t.OrganizationId = cte.parentId )
-- select
    --  ss.OrganizationId,ss.parentId, ss.settingName,ss.settingValue
-- from 
    select
        cte.OrganizationId,
        cte.ParentId,
        s.settingName ,
        s.settingValue,
        level
    from
        cte
    inner join Settings s on
        s.organizationId = cte.OrganizationId
        -- as ss group by ss.settingName
        

This gives me settings from all the organizations but i need the values of most recent child(if there is any) else from the parent (This should continue till the ROOT organization, bottom to up approach)

Settings Table Data

Organization table Data

Answer 1

Keep track of orgId of interest and level of the setting. Find first value by level

with recursive cte as (
   select  s.*, 1 as level, s.organizationId  as orgId
   from    Settings s 
   where   s.organizationId = 4
   union ALL 
   select   s.*, cte.level+1, cte.orgId
   from cte 
   join    Organization t on t.organizationId = cte.organizationId 
   join    settings s on s.organizationId = t.ParentId  
)
select distinct orgid, settingname, first_value(settingvalue) over(partition by orgId, settingname order by level)
from cte

EDIT Case when an organization can have no settings

with recursive cte as (
   select  t.organizationId, s.settingname, s.settingvalue, 1 as level, t.organizationId orgId
   from    Organization t
   left join Settings s on t.organizationId = s.organizationId 
   where   t.organizationId = 5
   
   union ALL 
   
   select   t.ParentId, s.settingname, s.settingvalue, cte.level+1, cte.orgId
   from cte 
   join    Organization t on t.organizationId = cte.organizationId 
   left join    settings s on s.organizationId = t.ParentId  
)
select distinct orgid, settingname, first_value(settingvalue) over(partition by orgId, settingname order by level)
from  cte
where settingName is not null 
order by orgid, settingname;

db<>fiddle