Hello,

I have Temp table and It looks like below

ParentRow       Row
------------------------
Null           1800
1800           1140
1800           1180
1800           1230
1800           1490
1140           1
1140           2
1180           3
1180           4
1230           5
1490           6

So Here 1800 is at Root Level
1800 is Parent of 1140,118,1230,1490 ---- Level 1
Same way We have Level 2 data
NowI want to show only data Root level an level1 so my output should be like as below

ParentRow       Row
------------------------
Null           1800
1800           1140
1800           1180
1800           1230
1800           1490

Hi,

Try use cte

DECLARE @parentchild table
(
 ParentRow int,
 [Row]  int
)
 
 insert @parentchild values (Null,1800),(1800,1140),(1800,1180),(1800,1230),(1800,1490),(1140,1),(1140,2),(1180,3),(1180,4),(1230,5),(1490,6)
 
 
 ;with cte as (
	select [Row],ParentRow, 0 as lvl from @parentchild where ParentRow is null
	UNION ALL
	SELECT c.[Row],c.ParentRow, p.lvl + 1 from @parentchild c 
		INNER JOIN cte p on c.ParentRow = p.Row 
)
SELECT * FROM cte WHERE lvl < 2

Please post the DDL next time as I am doing. If you as root intend the max of the column row the cleanest solution is:

create table #forum (parentrow int, row int)
insert into #forum values (null,1800),(1800,1140),(1800,1180),(1800,1230),(1800,1490),(1140,1),(1140,2),(1180,3),(1180,4),
(1230,5),(1490,6)

select * from #forum where parentrow=(select MAX(row) from #forum) or row=(select MAX(row) from #forum)

Please mark as answer if this post helped you

try with self join concept.

Try below

DECLARE @parentchild table
(
 ParentRow int,
 [Row]  int
)
 
 insert @parentchild values (Null,1800),(1800,1140),(1800,1180),(1800,1230),(1800,1490),(1140,1),(1140,2),(1180,3),(1180,4),(1230,5),(1490,6)
 
 select b.[Row],b.ParentRow, 0 as lvl from @parentchild b  where b.ParentRow is null
 union all
 select b.[Row],b.ParentRow, 1 as lvl from @parentchild a join @parentchild b on a.[Row]=b.ParentRow
 where a.ParentRow is null

Thanks

Saravana Kumar C