Find leaf level from organization table

Question

Hi!
I need to find leaf levels in an organizational table with some type of query. The rownr are unike. But the leaf level could be found in any level and consist of one or multiple rows before going up one one or many levels and then down to leaf level again. In this example i would like to find rownr, 3, 4, 8, 10 and 11. But my table consist of some thousand units but if a query could find leaf levels for this example i think it should work for table also. I have tried to solve this but i cant find a solution so i hope someone can help me.
Br
Arne

The structure looks like below
Rownr Level
1 1
2 2
3 3
4 3
5 2
6 3
7 4
8 5
9 4
10 5
11 5

Answer 1

Create Table #Sample(Rownr int, Level int);
Insert #Sample(Rownr, Level) Values
(1, 1),
(2, 2),
(3, 3),
(4, 3),
(5, 2),
(6, 3),
(7, 4),
(8, 5),
(9, 4),
(10, 5),
(11, 5);

;With cte As
(Select s.Rownr, Level, Lead(s.Level) Over(Order By s.Rownr) As NextLevel
From #Sample s)
Select c.Rownr
From cte c
Where c.Level >= IsNull(c.NextLevel, c.Level)
Order By Rownr;

go
Drop Table #Sample;

Tom

Answer 2

@Olsson Arne , try this:

SELECT s1.*  
FROM #Sample AS s1  
INNER JOIN #Sample AS s2 ON s1.Rownr + 1 = s2.Rownr AND s1.Level >= s2.Level  

Thank you @Tom Cooper for the CREATE TABLE ... and INSERT ... statements.

Answer 3

Please see:

https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child

Answer 4

Hi @Olsson Arne ,

Your rule description is not very clear. In this example, there are many ways to find 3, 4, 8, 10, and 11, but these methods may not apply to all the data in your table. So if this does not solve your problem, then please post more detailed rules.
Please refer to:

    Create Table #Sample(Rownr int, Level int);  
     Insert #Sample(Rownr, Level) Values  
     (1, 1),  
     (2, 2),  
     (3, 3),  
     (4, 3),  
     (5, 2),  
     (6, 3),  
     (7, 4),  
     (8, 5),  
     (9, 4),  
     (10, 5),  
     (11, 5);  
      
;With cte As  
 (Select Rownr, Level,(Rownr-Level) diff  
 From #Sample )  
 ,cte2 as  
(select diff,max(Rownr) Rownr from cte group by diff)  
  
select Rownr from cte2  
  
Drop Table #Sample  

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.