Find children of a most top level parent

Question

There are similar question asking how to find the top level parent of a child (this, this and this). I have a similar question but I want to find all childern of a top level parent. This is similar question but uses wordpress predefined functions.

sample table:

id    parent
1     0
2     0
3     1
4     2
5     3
6     3
7     4

I want to select ID with most top parent equals 1. The output should be 3 and all children of 3 I mean (5,6) and even more deep level children if available.

I know I can select them using two times of inner join but the hirearchy may be more complex with more levels.

https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy — PM 77-1, Oct 18 '18 at 21:05

score 0 · Answer 1 · answered Oct 18 '18 at 21:04

0

Since you aren't climbing the entire ladder...

select *
from YourTable
where parent = (select top 1 parent from YourTable group by parent order by count(parent) desc)

If you were wanting to return the parent of 3, since 3 was listed most often, then you'd use a recursive CTE.

answered Oct 18 '18 at 21:04

S3S

24,809
5
26
45

Thanks, But this returen all parents of a certain child. I need all children of a certain parent. Other answer seems working. – Ali Sheikhpour Oct 18 '18 at 21:12

The Impaler · Accepted Answer · 2018-10-18T21:16:16.773

0

A simple "Recursive CTE" will do what you want:

with n as (
  select id from my_table where id = 1 -- starting row(s)
  union all
  select t.id
    from n
    join my_table t on t.parent_id = n.id
)
select id from n;

This CTE will go down all levels ad infinitum. Well... by default SQL Server limits it to 128 levels (that you can increase to 65k).

edited Oct 18 '18 at 21:16

answered Oct 18 '18 at 21:04

The Impaler

45,731
9
39
76

Find children of a most top level parent

2 Answers2