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

yes, it gives me all the settings, i need the setting from bottom to up approach. so basically settingName will be unique, since the most updated setting will be taken up from the child. — sliceh, Jul 05 '21 at 04:57
Please, describe the rule to get settings. Why expected output contains `(d, 4)` for organisation A? And provide source data in tabular format or as insert statement. — astentx, Jul 05 '21 at 05:46
@astentx provided the data as part of image, it should get (d,4) for organization D as it has to traverse up towards the root and get only the values with recent updates, if there are no updated get the older one. — sliceh, Jul 05 '21 at 05:51
You have some predefined set of settings and want to get values for each setting from the node itself or from it's parent, if the node doesn't have this setting. Am I right? — astentx, Jul 05 '21 at 06:29
Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Jul 05 '21 at 07:09

Serg · Accepted Answer · 2021-07-05T08:52:17.257

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

edited Jul 05 '21 at 08:52

answered Jul 05 '21 at 06:48

Serg

22,285
5
21
48

It is useful to a certain extent. Thank you good sir. Creating a new organization with empty setting should give the settings from the above child, if exists else fetch from parent. currently it returns empty list. – sliceh Jul 05 '21 at 07:13
Organization 4 has exactly one setting. What is wrong with it ? – Serg Jul 05 '21 at 07:16
See edit for an organization with no settings case . – Serg Jul 05 '21 at 07:33
Thank you sir. it works but one more issue. Creating few new organizations with empty settings should give the settings from the above child, if exists else fetch from parent. currently it returns empty list. Like (A->B->C->D->E) D and E are empty. If i query for E, it should give values from C. – sliceh Jul 05 '21 at 08:15
Organization 4 with one setting works. Apologies, my bad. – sliceh Jul 05 '21 at 08:19
Updated , left join in both parts of the cte. – Serg Jul 05 '21 at 08:53
Could you please also explain the whole query as i am not much proficient in query. – sliceh Jul 05 '21 at 09:30
The cte is very similar to your original one with an addition of `left join settings` to get a level associated with the setting value. And select from cte uses `first_value` window function (see docs) to get a value assotiated with a minimum level. `distinct` just excludes intermediate values along the hierarchy path as we've got `first_value` . – Serg Jul 05 '21 at 09:37
Hi Serg, I am seeing that this query takes a lot of CPU usage. Do you have any solution for this? – sliceh Feb 01 '22 at 04:26
@silech, This is rather old question. You may wish to ask a new one to attract a wider audience attention to the problem. One possible way is to move `left join Settings` from the cte to the outer select. – Serg Feb 01 '22 at 07:35

score 0 · Answer 2 · edited Jul 06 '21 at 13:14

Create schema

create table t42 (id number, parent_id number, flag varchar2(1), str
varchar2(20));

insert into t42 values (1, null, 'A', 'Parent');

insert into t42 values (2, 1, 'B', 'Child 1'); insert into t42
values (3, 1, 'C', 'Child 2'); insert into t42 values (4, 1, 'C',
'Child 3');

insert into t42 values (5, 2, 'D', 'Grandchild 1 1'); insert into
t42 values (6, 3, 'B', 'Grandchild 2 1'); insert into t42 values (7,
3, 'D', 'Grandchild 2 2');

now excecute below query

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'A';

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'B';

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'C';

select t.id, t.parent_id, t.flag, t.str
from (
  select t.*, dense_rank() over (partition by flag order by lvl) as rn
  from (
    select t.*, level as lvl
    from t42 t
    start with parent_id is null
    connect by prior id = parent_id
  ) t
) t
where rn = 1
and flag = 'D';

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

2 Answers2