Find children of a most top level parent

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.

Solution

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).