question

OlssonArne-8894 avatar image
0 Votes"
OlssonArne-8894 asked EchoLiu-msft edited

Find leaf level from organization table

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 edited
 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered $$ANON_USER$$ commented

@OlssonArne-8894, 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 @TomCooper-6989 for the CREATE TABLE ... and INSERT ... statements.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I believe that to get also line 11 we have to modify the JOIN. For example:

 select s1.* 
  from #Sample s1 
  inner join #Sample s2 on 
      s2.Rownr = 
      (
          case when exists(select 1 from #Sample where Rownr=s1.Rownr+1) 
              then s1.Rownr+1 
              else s1.Rownr 
          end
      ) 
      and s1.Level >= s2.Level
0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @OlssonArne-8894,

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.




image.png (1.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.