none
How to find TopLevel Company in a table

    Question

  • CREATE TABLE #Organizations (ID INT, Number INT, Name varchar(30), SuperiorOrganizationID INT)
    INSERT INTO #Organizations VALUES
    (1, 1, 'Dell', NULL),
    (2, 2, 'HP', NULL),
    (3, 100, 'IBM World', NULL),
    (4, 150, 'IBM Europe', 3),
    (5, 159, 'IBM Germany', 4)
    
    Select Number, Name, TopMostOrganizationID = 'TO SOLVE' From #Organizations
    
    DROP TABLE #Organizations

    I need to solve the column TopMostOrganizationID. The result should be:

    1, 'Dell', 1
    
    2, 'HP', 2
    
    100, 'IBM World', 3
    
    150, 'IBM Europe', 3
    
    159, 'IBM Germany', 3

    Any idea?


    ... Podlesnick ...

    Saturday, May 18, 2013 11:37 AM

Answers

  • You can try CTE http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

    ;with cte(ID,Number, Name, SuperiorOrganizationID,TopMostOrganizationID)
    AS 
    (
    Select ID,Number, Name, SuperiorOrganizationID,ID as TopMostOrganizationID   From #Organizations where SuperiorOrganizationID is null 
    UNION ALL
    Select A.ID,A.Number, A.Name, A.SuperiorOrganizationID,B.TopMostOrganizationID as TopMostOrganizationID   From #Organizations A 
    INNER JOIN cte B
    ON A.SuperiorOrganizationID= B.ID
    )
    select A.Number, A.Name, A.TopMostOrganizationID from cte A


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat Babu (SS) Saturday, May 18, 2013 12:07 PM
    • Marked as answer by Podlesnick Saturday, May 18, 2013 12:28 PM
    • Unmarked as answer by Podlesnick Thursday, July 18, 2013 8:35 AM
    • Marked as answer by Podlesnick Thursday, July 18, 2013 8:35 AM
    Saturday, May 18, 2013 12:06 PM

All replies

  • Add a parent company column (like IBM) and use the RANK() function:

    http://blogs.msdn.com/b/craigfr/archive/2008/03/31/ranking-functions-rank-dense-rank-and-ntile.aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, May 18, 2013 11:47 AM
    Moderator
  • im going to try that

    ... Podlesnick ...

    Saturday, May 18, 2013 11:51 AM
  • well but how do you mean - add a parent company column (like IBM) - this is the problem there is a nested structure of three companies of which one is the topmost...

    ... Podlesnick ...

    Saturday, May 18, 2013 11:54 AM
  • You can try CTE http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

    ;with cte(ID,Number, Name, SuperiorOrganizationID,TopMostOrganizationID)
    AS 
    (
    Select ID,Number, Name, SuperiorOrganizationID,ID as TopMostOrganizationID   From #Organizations where SuperiorOrganizationID is null 
    UNION ALL
    Select A.ID,A.Number, A.Name, A.SuperiorOrganizationID,B.TopMostOrganizationID as TopMostOrganizationID   From #Organizations A 
    INNER JOIN cte B
    ON A.SuperiorOrganizationID= B.ID
    )
    select A.Number, A.Name, A.TopMostOrganizationID from cte A


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat Babu (SS) Saturday, May 18, 2013 12:07 PM
    • Marked as answer by Podlesnick Saturday, May 18, 2013 12:28 PM
    • Unmarked as answer by Podlesnick Thursday, July 18, 2013 8:35 AM
    • Marked as answer by Podlesnick Thursday, July 18, 2013 8:35 AM
    Saturday, May 18, 2013 12:06 PM
  • Ssarat Babu, it works like a charm. Thank you very much

    ... Podlesnick ...

    Saturday, May 18, 2013 12:29 PM
  • Sarat and what if I want to make it as a computed column for the same table?

    ... Podlesnick ...

    Saturday, May 18, 2013 12:31 PM
  • I am afraid you cant use this logic to achieve this.

    Instead , during new row(company) inserts this can be calculated and inserted as new column value, may be the Proc where the data insert is happening can be modified to do this. 

    Still you have option of using trigger to update the value for the new column, but it will be an additional over head on CPU , if the rows inserted into this table too frequently.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat Babu (SS) Saturday, May 18, 2013 12:46 PM corrected spelling
    Saturday, May 18, 2013 12:40 PM
  • Forgot to mention, thanks for posting the DDL, Sample data and output .

    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, May 18, 2013 1:24 PM
  • That logic does not work if I just add one more line:

    CREATE TABLE #Organizations (ID INT, Number INT, Name varchar(30), SuperiorOrganizationID INT)
    INSERT INTO #Organizations VALUES
    (1, 1, 'Dell', NULL),
    (2, 2, 'HP', NULL),
    (3, 100, 'IBM World', NULL),
    (4, 150, 'IBM Europe', 3),
    (5, 159, 'IBM Germany', 4),
    (6, 200, 'Dell Asia', 5)
    
    
    ;with cte(ID,Number, Name, SuperiorOrganizationID,TopMostOrganizationID)
    AS 
    (
    Select ID,Number, Name, SuperiorOrganizationID,ID as TopMostOrganizationID   
    From #Organizations where SuperiorOrganizationID is null 
    UNION ALL
    Select A.ID,A.Number, A.Name, A.SuperiorOrganizationID,B.TopMostOrganizationID as TopMostOrganizationID   
    From #Organizations A 
    INNER JOIN cte B
    ON A.SuperiorOrganizationID= B.ID
    )
    select A.Number, A.Name, A.TopMostOrganizationID from cte A
    GO
    DROP TABLE #Organizations;
    

    Number Name TopMostOrganizationID
    1 Dell 1
    2 HP 2
    100 IBM World 3
    150 IBM Europe 3
    159 IBM Germany 3
    200 Dell Asia 3

    You need to add a parent company column like IBM for the IBM companies.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, May 18, 2013 1:25 PM
    Moderator
  • Kalman i dont get it. I tried your 'not working' solution, but it works - Dell Asia has Superior Organization IBM germany, which is under ibm europe, which is under ibm world so output ID 3 is good for ibm world...

    ... Podlesnick ...

    Saturday, May 18, 2013 1:30 PM
  • How the tree is defined? By position?

    Normally, self-referencing FOREIGN KEY (Employee table in AdventureWorks) or hierarchyid (Employee table in AdventureWorks2012) is used for tree structure setup.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, May 18, 2013 4:08 PM
    Moderator
  • As you can see, the tree is defined by last column in table = 
    SuperiorOrganizationID

    ... Podlesnick ...

    Saturday, May 18, 2013 6:23 PM
  • Can you add to your table "Dell Asia" and "Dell Europe" and repost the script? Thanks.

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, May 18, 2013 7:04 PM
    Moderator
  • here you are:

    CREATE TABLE #Organizations (ID INT, Number INT, Name varchar(30), SuperiorOrganizationID INT)
    INSERT INTO #Organizations VALUES
    (1, 1, 'Dell', NULL),
    (2, 2, 'HP', NULL),
    (3, 100, 'IBM World', NULL),
    (4, 150, 'IBM Europe', 3),
    (5, 159, 'IBM Germany', 4),
    (6, 200, 'Dell Asia', 1),
    (7, 300, 'Dell Europe', 1)
    
    
    ;with cte(ID,Number, Name, SuperiorOrganizationID,TopMostOrganizationID)
    AS 
    (
    Select ID,Number, Name, SuperiorOrganizationID,ID as TopMostOrganizationID   
    From #Organizations where SuperiorOrganizationID is null 
    UNION ALL
    Select A.ID,A.Number, A.Name, A.SuperiorOrganizationID,B.TopMostOrganizationID as TopMostOrganizationID   
    From #Organizations A 
    INNER JOIN cte B
    ON A.SuperiorOrganizationID= B.ID
    )
    select A.Number, A.Name, A.TopMostOrganizationID from cte A
    GO
    DROP TABLE #Organizations;

    Number Name TopMostOrganizationID
    1 Dell 1
    2 HP 2
    100 IBM World 3
    150 IBM Europe 3
    159 IBM Germany 3
    200 Dell Asia 1
    300 Dell Europe 1

    Since we have Dell in Number = 1 then both Dell Asia and Europe are its subordinates.


    ... Podlesnick ...

    Saturday, May 18, 2013 7:19 PM