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.
8,560 questions
No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,026 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

    No comments

3 additional answers

Sort by: Most helpful
  1. Guoxiong 7,681 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. Tom Phillips 17,511 Reputation points
    2020-09-15T16:00:45.427+00:00
    No comments

  3. EchoLiu-MSFT 14,416 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.

    No comments