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