Post reply

How to find ultimate parent

  • October 25, 2023 at 4:35 pm

    #4314749

    Hi

    I have dataset like this

     

    drop table if exists #dataset

    create table #dataset(CIF VARCHAR(255), PARENT_CIF VARCHAR(255), UP_CIF VARCHAR(255))

    INSERT INTO #dataset

    SELECT 45218547, 54044082, 54044082 union all

    SELECT 54044082, 64349595, 64349595 union all

    SELECT 59741694, 54044082, 54044082 union all

    SELECT 64349595, 59741694, 59741694 union all

    SELECT 189, 345978, 345978 union all

    SELECT 22002363, 189, 189 union all

    SELECT 28459008, 22002363, 22002363 union all

    SELECT 29135817, 28459008, 28459008 union all

    SELECT 29154411, 29135817, 29135817

     

    and my expected result is like this

     

    CIF Parent_CIF UP_CIF

    45218547 54044082 64349595

    54044082 64349595 64349595

    59741694 54044082 64349595

    64349595 59741694 64349595

    189 345978 345978

    22002363 189 345978

    28459008 22002363 345978

    29135817 28459008 345978

    29154411 29135817 345978

    how to get this result set

     

    • This topic was modified 1 year, 1 month ago by  inayatkhan.

  • October 25, 2023 at 5:19 pm

    #4314785

    Based on the last five rows, it looks like you would want to use a CTE to go back through ancestors (parent -> grandparent -> great-grandparent -> etc. until you got to the earliest ancestor, correct?

    But 54044082, 59741694,  & 64349595 are involved in cyclic references. What rule are you using to arbitrarily stop the first ancestries at 64349595? You're not merely stopping at the first cycle because

    • 45218547 would stop at 59741694
    • 54044082 would stop at 59741694
    • 64349595 would stop at 54044082

      SSC_ParentCIFs

  • October 25, 2023 at 5:22 pm

    #4314786

    All perfect parents have the same thing in common.  No kids

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • October 25, 2023 at 5:44 pm

    #4314813

     

    Like Rule is When first  cyclic references happen stop on that place.

    Like

    45218547  --> 54044082 --> 64349595 --> 59741694 -->54044082 so his parecnt_cif is ultimate parent 64349595

    54044082 --> 64349595 --> 59741694 -->54044082  so his parecnt_cif is ultimate parent 64349595

    59741694 --> 54044082 --> so his parecnt_cif is ultimate parent 64349595

    64349595 --> 59741694 --> 54044082 --> so his parecnt_cif is ultimate parent 64349595

    is this possible?

  • October 25, 2023 at 9:18 pm

    #4314891

    inayatkhan wrote:

    Hi

    I have dataset like this

    drop table if exists #dataset create table #dataset(CIF VARCHAR(255), PARENT_CIF VARCHAR(255), UP_CIF VARCHAR(255))

    INSERT INTO #dataset SELECT 45218547, 54044082, 54044082 union all SELECT 54044082, 64349595, 64349595 union all SELECT 59741694, 54044082, 54044082 union all SELECT 64349595, 59741694, 59741694 union all SELECT 189, 345978, 345978 union all SELECT 22002363, 189, 189 union all SELECT 28459008, 22002363, 22002363 union all SELECT 29135817, 28459008, 28459008 union all SELECT 29154411, 29135817, 29135817

    and my expected result is like this

    CIF Parent_CIF UP_CIF 45218547 54044082 64349595 54044082 64349595 64349595 59741694 54044082 64349595 64349595 59741694 64349595 189 345978 345978 22002363 189 345978 28459008 22002363 345978 29135817 28459008 345978 29154411 29135817 345978

    how to get this result set

    As a bit of a side, unless your CIFs have non-numeric characters, storing them as VARCHAR(255) is one of the very worst ideas when it come to such hierarchies.  The use of fixed width integer types opens up whole new worlds of possibilities.

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets

    https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply