Finding a Top Level Parent in SQL

Finding a Top Level Parent in SQL

You can use recursive CTE to achieve that:

DECLARE @childID INT 
SET @childID  = 1 --chield to search

;WITH RCTE AS
(
    SELECT *, 1 AS Lvl FROM RelationHierarchy 
    WHERE ChildID = @childID

    UNION ALL

    SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
)
SELECT TOP 1 id, Name
FROM RCTE r
inner JOIN dbo.Person p ON p.id = r.ParentId
ORDER BY lvl DESC

SQLFiddle DEMO

EDIT - for updated request for top level parents for all children:

;WITH RCTE AS
(
    SELECT  ParentId, ChildId, 1 AS Lvl FROM RelationHierarchy 

    UNION ALL

    SELECT rh.ParentId, rc.ChildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.ChildId = rc.ParentId
)
,CTE_RN AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY r.ChildID ORDER BY r.Lvl DESC) RN
    FROM RCTE r

)
SELECT r.ChildId, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM CTE_RN r
INNER JOIN dbo.Person pp ON pp.id = r.ParentId
INNER JOIN dbo.Person pc ON pc.id = r.ChildId
WHERE RN =1

SQLFiddle DEMO

EDIT2 - to get all persons change JOINS a bit at the end:

SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN CTE_RN r ON pc.id = r.CHildId AND  RN =1
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId

SQLFiddle DEMo

Finding Top Level Parent ID

Yet another shorter CTE :

WITH cte AS(
      SELECT *, id AS topparent 
      FROM t 
      WHERE parentid IS NULL
  UNION ALL
      SELECT t.*, c.topparent 
      FROM t JOIN cte c ON c.id = t.parentid
      WHERE t.id <> t.parentid
)
SELECT * FROM cte

Tested here : SQL Fiddle

EDIT : This damn SQLfiddle is unresponsive again. Test DDL Code below

CREATE  table t (id INT, parentid INT);
INSERT INTO T VALUES (1  , NULL );
INSERT INTO T VALUES (2  , 1    );           
INSERT INTO T VALUES (3  , 2    );          
INSERT INTO T VALUES (9  , NULL );          
INSERT INTO T VALUES (5  , 9    );          
INSERT INTO T VALUES (6  , 5    );          
INSERT INTO T VALUES (25 , NULL );          
INSERT INTO T VALUES (30 , 25   );

Find top parent in hierarchy

I'm not really sure how it's different from any other recursive cte question, but since you've done such a great job posting sample data as DDL+DML I've decided to answer the question anyway (though it could have been just as easily closed as a duplicate).

As you wrote, using a recursive cte is the solution here:

WITH cte AS 
(
    SELECT Child, Parent, Child As TopParent
    FROM #Temp
    WHERE Parent IS NULL
    UNION ALL
    SELECT T.Child, T.Parent, cte.TopParent
    FROM #Temp As T
    JOIN cte 
        ON T.Parent = cte.Child
)

SELECT *
FROM cte
ORDER BY TopParent DESC, Parent;

Find children of a most top level parent

A simple "Recursive CTE" will do what you want:

with n as (
  select id from my_table where id = 1 -- starting row(s)
  union all
  select t.id
    from n
    join my_table t on t.parent_id = n.id
)
select id from n;

This CTE will go down all levels ad infinitum. Well... by default SQL Server limits it to 128 levels (that you can increase to 65k).

The most efficient way to find the top level parent in SQL Server?

SQL2008+:

To store hierarchies , SQL Server includes HIERARCHYID data type. Above data can be "converted" to use HIERARCHYID "values" thus:

catName     catID     parentID  hierarchyNode
=============================================
vehicles    1         0         /1/
cars        2         1         /1/2/
sedans      3         2         /1/2/3/
animals     4         0         /4/
cows        5         4         /4/5/

After conversion, I would drop parentID column.

HIERARCHYID is SQLCLR system data type which include following methods:

• HidValue.GetLevel()
• HidValue.GetAncestor(level).

To get parent node I would use these methods thus:

DECLARE @node HIERARCHYID
SET     @node = '/1/2/3/'

SELECT  
    currentNodeLvl= @node.GetLevel(),                                 --> 3
    parentAsHID   = @node.GetAncestor(@node.GetLevel() - 1),          --> 0x58
    parentAsString= @node.GetAncestor(@node.GetLevel() - 1).ToString()--> /1/

More, I would create an index on hierarchyNode column thus:

CREATE UNIQUE INDEX IUN_Table_hierarchyNode
ON dbo.Table(hierarchyNode)

and final query will be:

SELECT ..., prt.catID AS parentID
FROM dbo.Table crt -- Curent node
LEFT/INNER JOIN -- It depends on hierarchyID nullability 
dbo.MyTable prt -- Parent node
ON @node.GetAncestor(crt.hierarchyID.GetLevel() - 1).ToString() = prt.hierarchyID

Find top parent of child, multiple levels

You can use a hierarchical query and CONNECT_BY_ROOT.

Either starting at the root of the hierarchy and working down:

SELECT id,
       CONNECT_BY_ROOT(id) AS root_id
FROM   entry
WHERE  id IN (6, 3)
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

Or, from the entry back up to the root:

SELECT CONNECT_BY_ROOT(id) AS id,
       id AS root_id
FROM   entry
WHERE  parent_id IS NULL
START WITH id IN (6, 3)
CONNECT BY PRIOR parent_id = id;

Which, for the sample data:

CREATE TABLE entry( id, parent_id ) AS
SELECT 1, NULL FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 4, NULL FROM DUAL UNION ALL
SELECT 5, 4 FROM DUAL UNION ALL
SELECT 6, 5 FROM DUAL UNION ALL
SELECT 7, 6 FROM DUAL

Both output:

ID	ROOT_ID
3	1
6	4

SQL Server function to get top level parent in hierarchy

With recursive cte going from top to childs:

with cte as(
  select *, code as ultimate from t where code = under
  union all
  select t.*, c.ultimate from t
  join cte c on c.code = t.under
  where t.code <> t.under
)
select * from cte

For data:

create table t (code int, name varchar(100), under int)
insert into t values
(1, 'National Sales Manager', 1),
(2, 'regional sales manager', 1),
(3, 'area sales manager', 2),
(4, 'sales manager', 3),
(5, 'a', 5),
(6, 'b', 5),
(7, 'c', 5),
(8, 'd', 7),
(9, 'e', 7),
(10, 'f', 9),
(11, 'g', 9)

it generates the output:

code    name                    under   ultimate
1       National Sales Manager  1       1
5       a                       5       5
6       b                       5       5
7       c                       5       5
8       d                       7       5
9       e                       7       5
10      f                       9       5
11      g                       9       5
2       regional sales manager  1       1
3       area sales manager      2       1
4       sales manager           3       1

Fiddle http://sqlfiddle.com/#!6/17c12e/1

Find topmost parents in self-referential table

2 issues with the code:

1. You need to track your starting ID through the recursion;
2. The where condition in the recursive part is actually preventing you from getting the result.

As such:

WITH cte AS (
    SELECT ID, link, ID as [StartID]
    FROM @t
    WHERE ID IN (6, 7)
    UNION ALL
    SELECT t.ID, t.link, c.StartID
    FROM @t t
    JOIN cte c ON t.ID = c.link
)
SELECT c.StartID, c.ID
FROM cte c
where c.link is null;

SQL - Find the Top Level Parent and Multiply Quantities

For this problem I think you must use a recursive query. In fact I think SubAssembly table should have some ProductID field other than SubAssembly to easily identify the main product that contains assemblies.

You can find a similar example in SLQ Server documentation.

Can check it here: http://rextester.com/FQYI80157

Change the Qty in Config table to change the final result.

create temp table t1 (cfg int, part varchar(10), qty int);
create temp table t2 (part varchar(10), sasm varchar(10), qty int);
insert into t1 values (1,'A',2);
insert into t2 values ('A','AA',2);
insert into t2 values ('A','BB',4);
insert into t2 values ('A','CC',2);
insert into t2 values ('A','DD',4);
insert into t2 values ('AA','AAA',2);

WITH cte(sasm, part, qty) 
AS (
    SELECT sasm, part, qty 
    FROM #t2 WHERE part = 'A'

    UNION ALL

    SELECT p.sasm, p.part, p.qty * pr.qty
    FROM cte pr, #t2 p
    WHERE p.part = pr.sasm
  )
SELECT #t1.cfg, cte.part, cte.sasm, SUM(cte.qty * COALESCE(#t1.qty,1)) as total_quantity
FROM cte
left join #t1 on cte.part = #t1.part
GROUP BY #t1.cfg, cte.part, cte.sasm;

This is the result:

+------+------+----------------+
| part | sasm | total_quantity |
+------+------+----------------+
|   A  | AA   |        4       |
+------+------+----------------+
|   A  | DD   |        8       |
+------+------+----------------+
|  AA  | AAA  |        4       |
+------+------+----------------+
|   A  | BB   |        8       |
+------+------+----------------+
|   A  | CC   |        4       |
+------+------+----------------+

Finding Top level parent of each row of a table [SQL Server 2008]

I have also updated the answer in the original question, but never-mind, here is a copy also:

;WITH RCTE AS
(
    SELECT  ParentId, ChildId, 1 AS Lvl FROM RelationHierarchy 

    UNION ALL

    SELECT rh.ParentId, rc.ChildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.ChildId = rc.ParentId
)
,CTE_RN AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY r.ChildID ORDER BY r.Lvl DESC) RN
    FROM RCTE r

)
SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN CTE_RN r ON pc.id = r.CHildId AND  RN =1
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId

SQLFiddle DEMO

Note that the slight difference is in recursive part of CTE. ChildID is now rewritten each time from the anchor part. Also addition is ROW_NUMBER() function (and new CTE) to get the top level for each child at the end.

EDIT - Version2

After finding a performance issues with first query, here is an improved version. Going from top-to-bottom, instead of other way around - eliminating creating of extra rows in CTE, should be much faster on high number of recursions:

;WITH RCTE AS
(
    SELECT  ParentId, CHildId, 1 AS Lvl FROM RelationHierarchy r1
    WHERE NOT EXISTS (SELECT * FROM RelationHierarchy r2 WHERE r2.CHildId = r1.ParentId)

    UNION ALL

    SELECT rc.ParentId, rh.CHildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rc.CHildId = rh.ParentId
)
SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN RCTE r ON pc.id = r.CHildId
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId 

SQLFiddle DEMO

---

Related Topics

Generate Random Int Value from 3 to 6

Performance of Like '%Query%' VS Full Text Search Contains Query

How to Create a Stored Procedure That Will Optionally Search Columns

Union All VS or Condition in SQL Server Query

SQL Not Recognizing Column Alias in Where Clause

SQL Server for Xml Path Make Repeating Nodes

SQL Server Query Time Out Depending on Where Clause

How to Insert a String Which Contains an "&"

SQL Bulk Import from CSV

Hql Query to Check If Size of Collection Is 0 or Empty

Count Cumulative Total in Postgresql

Zero SQL Deadlock by Design - Any Coding Patterns

Getting Row Number for Query

Prepared Statement on Postgresql in Rails

MySQL Subquery Returns More Than One Row

Conditional Lead/Lag Function Postgresql

How to Compare Dates in SQL Server

What's the Correct Name for an "Association Table" (A Many-To-Many Relationship)

---

Leave a reply

Submit