Find leaf level from organization table

Olsson Arne 81 Reputation points
2020-09-15T14:47:01.44+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,585 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2020-09-15T15:09:35.727+00:00
    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

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2020-09-15T15:41:35.937+00:00

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


  2. EchoLiu-MSFT 14,571 Reputation points
    2020-09-16T09:38:53.227+00:00

    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  
    

    24989-image.png

    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.

    0 comments No comments