Home » SQL & PL/SQL » SQL & PL/SQL » Removing duplicate in Hierarchy Level (Oracle 11g, Windows 7)
Removing duplicate in Hierarchy Level [message #590427] Thu, 18 July 2013 04:00 Go to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Hi,
I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.
CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));

INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AOP');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_APG');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_AOV','TBL_APP');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_ABC');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_APA');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AAO');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AGE');
INSERT INTO NODE_LVL VALUES('TBL_AAO','TBL_AOO');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AAB');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_ADP');
INSERT INTO NODE_LVL VALUES('TBL_AAD','TBL_ADE');
INSERT INTO NODE_LVL VALUES('TBL_ADV','TBL_APP');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AAD');
INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_ADD');
INSERT INTO NODE_LVL VALUES('TBL_AAB','TBL_ABM');
INSERT INTO NODE_LVL VALUES('TBL_ACF','TBL_APL');

Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';

At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.
SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

I want to suppress such duplicates. So I added DISTINCT
SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;


BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.
Please let me know, if more info is required.

Thanks & Regards,
Lokesh
Re: Removing duplicate in Hierarchy Level [message #590429 is a reply to message #590427] Thu, 18 July 2013 04:26 Go to previous messageGo to next message
Littlefoot
Messages: 19635
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try ORDER SIBLINGS BY (which is used in hierarchy queries, do NOT use ORDER BY)?
Re: Removing duplicate in Hierarchy Level [message #590431 is a reply to message #590427] Thu, 18 July 2013 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about:
SELECT PARENT_NODE, CHILD_NODE, LEVEL 
FROM (select distinct * from NODE_LVL)
... 

Regards
Michel

[Updated on: Thu, 18 July 2013 04:31]

Report message to a moderator

Re: Removing duplicate in Hierarchy Level [message #590433 is a reply to message #590429] Thu, 18 July 2013 04:41 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks littlefoot and Michel for your response.

I tried order siblings by parent_node, child_node but didn't get required output
SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE
ORDER SIBLINGS BY ??;

and

SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM (select distinct * from NODE_LVL) START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;



Please suggest how to proceed.

Regards,
Lokesh
Re: Removing duplicate in Hierarchy Level [message #590438 is a reply to message #590433] Thu, 18 July 2013 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
didn't get required output


Which is? Post it for the data you gave.

Regards
Michel
Re: Removing duplicate in Hierarchy Level [message #590440 is a reply to message #590427] Thu, 18 July 2013 05:26 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hi,

lokimisc wrote on Thu, 18 July 2013 11:00
At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively

According to the values you provided in your test case the hierarchy is as following:
TBL_ACF          
    TBL_APL         
        TBL_AAB
            TBL_ABM
        TBL_AAD
            TBL_ADE
        TBL_AAO
            TBL_AOO
        TBL_ABC
        TBL_ADD
            TBL_ADW
        TBL_ADP
            TBL_ADV
                TBL_APP        -- Level 5   
                    TBL_ACS
        TBL_AFL
        TBL_AGE
        TBL_AOP
            TBL_ADW
            TBL_AOV
                TBL_APP        -- Level 5       
                    TBL_ACS
        TBL_APA
        TBL_APG


Now apparently you wish to remove one of the childs at level 5 as indicated above, that is removing
. . .
TBL_APP 
   TBL_ACS

But which one do you remove and based on what rule? The branch which is child of TBL_ADV or the one which belongs to TBL_AOV?

Regards,
Dariyoosh
Re: Removing duplicate in Hierarchy Level [message #590444 is a reply to message #590440] Thu, 18 July 2013 05:34 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks Dariyoosh for your response.
Removing duplicate is preferably on second or more occurrence i.e TBL_AOV

Regards,
Lokesh
Re: Removing duplicate in Hierarchy Level [message #590447 is a reply to message #590444] Thu, 18 July 2013 05:38 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
lokimisc wrote on Thu, 18 July 2013 12:34
Removing duplicate is preferably on second or more occurrence i.e TBL_AOV


That is precisely the problem, first you have to define, what does SECOND mean according to the context of your hierarchy? how do you compare occurrences and based on what rule can you say for example TBL_ADV is the first occurrence and TBL_AOV is the second?

Regards,
Dariyoosh
Re: Removing duplicate in Hierarchy Level [message #590454 is a reply to message #590447] Thu, 18 July 2013 05:54 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Hmm..
As I mentioned in the beginning, if there are any node which is having 2 different parent nodes and which results in same level of hierarchy for two or more nodes then query should retain only first occurrence of level.

I know it is very typical requirement.
So if possible please help in the logic that simply remove the duplicates from the resultant (without any business rules). Later I will try to modify as per the requirement.

Thanks
Lokesh
Re: Removing duplicate in Hierarchy Level [message #590457 is a reply to message #590454] Thu, 18 July 2013 06:00 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
If you read carefully my previous comment you will (I hope) admit that there cannot be any solution for this problem without specifying clearly a rule. You implement a solution based on a specific/clear rule.

Regards,
Dariyoosh
Re: Removing duplicate in Hierarchy Level [message #590468 is a reply to message #590457] Thu, 18 July 2013 06:26 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Your first SELECT has no ORDER BY:
 SELECT PARENT_NODE, CHILD_NODE, LEVEL 
   FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' 
CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

PARENT_NODE, CHILD_NODE, LEVEL 
-------------------------
TBL_ACF	TBL_APL	1
TBL_APL	TBL_AAB	2
TBL_AAB	TBL_ABM	3
TBL_APL	TBL_AAD	2
TBL_AAD	TBL_ADE	3
TBL_APL	TBL_AAO	2
TBL_AAO	TBL_AOO	3
TBL_APL	TBL_ABC	2
TBL_APL	TBL_ADD	2
TBL_ADD	TBL_ADW	3
TBL_APL	TBL_ADP	2
TBL_ADP	TBL_ADV	3
TBL_ADV	TBL_APP	4
TBL_APP	TBL_ACS	5
TBL_APL	TBL_AFL	2
TBL_APL	TBL_AGE	2
TBL_APL	TBL_AOP	2
TBL_AOP	TBL_ADW	3
TBL_AOP	TBL_AOV	3
TBL_AOV	TBL_APP	4
TBL_APP	TBL_ACS	5
TBL_APL	TBL_APA	2
TBL_APL	TBL_APG	2

It seems that ORACLE nevertheless gives the result in a specific ORDER similar to SEARCH DEPTH FIRST in RECURSIVE SUBQUERY FACTORING (since ORACLE 11g2), but this is not guarenteed?
You want to remove duplicates in CHILD_NODE , PARENT_NODE.
So you could (with the same restriction as in your original query) - save the originale rownum (rn), find the duplicates and eliminate them:
SELECT parent_node, child_node, lv 
       --,rn, rk
  FROM 
  (SELECT parent_node, child_node, level lv, rownum rn, ROW_NUMBER() OVER (PARTITION BY PARENT_NODE, CHILD_NODE ORDER BY 1) rk 
    FROM node_lvl 
   START WITH parent_node = 'TBL_ACF' 
 CONNECT BY PRIOR child_node = parent_node)
 WHERE rk=1
 ORDER BY rn; 

PARENT_NODE, CHILD_NODE, LEVEL 
-------------------------
TBL_ACF	TBL_APL	1
TBL_APL	TBL_AAB	2
TBL_AAB	TBL_ABM	3
TBL_APL	TBL_AAD	2
TBL_AAD	TBL_ADE	3
TBL_APL	TBL_AAO	2
TBL_AAO	TBL_AOO	3
TBL_APL	TBL_ABC	2
TBL_APL	TBL_ADD	2
TBL_ADD	TBL_ADW	3
TBL_APL	TBL_ADP	2
TBL_ADP	TBL_ADV	3
TBL_ADV	TBL_APP	4
TBL_APP	TBL_ACS	5
TBL_APL	TBL_AFL	2
TBL_APL	TBL_AGE	2
TBL_APL	TBL_AOP	2
TBL_AOP	TBL_ADW	3
TBL_AOP	TBL_AOV	3
TBL_AOV	TBL_APP	4
TBL_APL	TBL_APA	2
TBL_APL	TBL_APG	2 

[Updated on: Thu, 18 July 2013 06:27]

Report message to a moderator

Re: Removing duplicate in Hierarchy Level [message #590471 is a reply to message #590468] Thu, 18 July 2013 06:43 Go to previous message
lokimisc
Messages: 72
Registered: February 2008
Member
WOW...!!!
Thanks a lot _jum .. Smile

Regards,
Lokesh
Previous Topic: Partition Exchange - Performance
Next Topic: Column To Row Generation Help Required
Goto Forum:
  


Current Time: Mon Sep 22 18:17:34 CDT 2014

Total time taken to generate the page: 1.47396 seconds