How to get minimum level value record for each group

Transact-SQL
1

Dear Team,

I have sample data like below
Parent Child Level
39358 39357 -2
39357 39353 -1
39353 0 0
39351 39350 -1
39349 0 0

How to get minimum level value record for each group.
output should be like below for this data.

Parent Child Level
39358 39357 -2
39351 39350 -1

Thanks in advance.

2

something like:

select max(parent), max(child), level
from mydata
where level <> 0
group by level
3

It would appear that you're looking for the "Leaf Level Nodes'. I'll also bring attention to the facts that...

  1. You appear to have the column names for Parent and Child backwards.
  2. You actually have 3 leaf levels in the data that you presented because the second to the last line is actually an "orphan" that forms it's own "stump" for a tree. As a result of the data, the code below correctly finds 3 leaf nodes.

With that in mind, here's one of the proper methods for finding "Leaf Level Nodes" for a "clean Adjacency List".
--===== Create the test table.
-- This is NOT a part of the solution.
SELECT *
INTO #TestTable
FROM (
SELECT 39358,39357,-2 UNION ALL
SELECT 39357,39353,-1 UNION ALL
SELECT 39353,0 , 0 UNION ALL
SELECT 39351,39350,-1 UNION ALL
SELECT 39349,0 , 0
)d(Parent,Child,[Level])
;
--===== Find all "Leaf Level Nodes" in the tree.
-- A "Leaf Level Node" is a node with no "downline"/descendents.
SELECT a.*
FROM #TestTable a
WHERE a.Parent NOT IN (SELECT b.Child FROM #TestTable b)
;

1 Like
4

Thank you Jeff, perfect answer and much appreciated.

Thank you gbritton too.

5

@satya, thanks for the feedback.