How to get Number of levels in sub_of using sql se

Question

I have table like this,

|id | Name   |sub_of|
|1  |Level 1A|  0   |
|2  |Level 1B|  0   |
|3  |Level 1C|  0   |
|4  |Level 1D|  0   |
|5  |Level 2A|  1   |
|6  |Level 2B|  2   |
|7  |Level 2C|  3   |
|8  |Level 3C|  7   |
|9  |Level 4C|  8   |
|10 |Level 5C|  9   |
|11 |Level 3B|  6   |

I this case

• A have 2 levels
• B have 3 levels
• c have 5 levels
• d have 1 levels

Sub_of is id of the previous level. Name does not contain sub_of name(ie A does not contains all level of A).

I need Maximum level of the sub_of. Please give some ideas.

Answer 1

Recursive cte is your friend...

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    id int,
    Name varchar(10), 
    sub_of int
)

INSERT INTO @T VALUES
(1  ,'Level 1A',0),
(2  ,'Level 1B',0),
(3  ,'Level 1C',0),
(4  ,'Level 1D',0),
(5  ,'Level 2A',1),
(6  ,'Level 2B',2),
(7  ,'Level 2C',3),
(8  ,'Level 3C',7),
(9  ,'Level 4C',8),
(10 ,'Level 5C',9),
(11 ,'Level 3B',6)

The cte:

;WITH CTE AS
(
    SELECT id, Name, Sub_Of, 0 As Level
    FROM @T
    WHERE Sub_Of = 0

    UNION ALL
    SELECT t.id, t.Name, t.Sub_Of, Level + 1 
    FROM @T as t
    INNER JOIN CTE ON t.Sub_Of = CTE.id  
)

The query:

SELECT *
FROM CTE 
ORDER BY Level DESC

Results:

id  Name        Sub_Of  Level
10  Level 5C    9       4
9   Level 4C    8       3
11  Level 3B    6       2
8   Level 3C    7       2
7   Level 2C    3       1
6   Level 2B    2       1
5   Level 2A    1       1
1   Level 1A    0       0
2   Level 1B    0       0
3   Level 1C    0       0
4   Level 1D    0       0

Answer 2

You need a unique identifier for each level (A, B, C, ...)

Using a recursive cte query I've used the first id where sub_of = 0 as a unique identifier:

;WITH tree AS
(
    SELECT c1.id, c1.name, c1.sub_of, [id_level] = c1.id, [level] = 1
    FROM dbo.[btree2] c1
    WHERE c1.sub_of = 0
    UNION ALL
    SELECT c2.id, c2.name, c2.sub_of, [id_level] = tree.[id_level], [level] = tree.[level] + 1
    FROM dbo.[btree2] c2 INNER JOIN tree ON c2.sub_of = tree.id
)
SELECT t1.id, t1.sub_of, t1.name, t1.id_level, t1.level
FROM tree t1
     INNER JOIN (SELECT id_level, MAX(level) AS level FROM tree GROUP BY id_level) t2
     ON t1.id_level = t2.id_level AND t1.level = t2.level
ORDER BY id_level, level
OPTION (MAXRECURSION 0)
;

Then simply get MAX(level) for each group.

+----+--------+----------+----------+-------+
| id | sub_of | name     | id_level | level |
+----+--------+----------+----------+-------+
| 5  |    1   | Level 2A |     1    |   2   |
| 11 |    6   | Level 3B |     2    |   3   |
| 10 |    9   | Level 5C |     3    |   5   |
| 4  |    0   | Level 1D |     4    |   1   |
+----+--------+----------+----------+-------+

Check it here: http://rextester.com/KIEY97438