Home » SQL & PL/SQL » SQL & PL/SQL » Assign same Group id to rows
Assign same Group id to rows [message #672191] Sat, 06 October 2018 07:06 Go to next message
purnima1
Messages: 76
Registered: June 2014
Member
I have one table which has data following manner. currently group id is null.As we can see all the entries are related to each other even indirectly .

15 is related to 111 which is related to 17 which is related to 16 , So in all are interrelated so we need to assign same group id to this .
When I am trying to create hierarchical query , it is placing 16>17 relation before 16> 2209 . Is there way 16>2209 comes before 16>17 .

I am first placing data in collection by first putting creating hierarchical query and then written small function to assign grp id. Problem I am getting is since 16>17 is coming before 16>2209 so function is unable to identify that this group also related to 15 >111.

Gold_id rel_gold_id    grp_id 
15	111	
15	2209	
16	17	
16	2209	
111	17	
17	24	

If it is confusing then I will also share code where I am generating grp id . But for me current prblm is in hierarchical query

[mod-edit: code tags added by bb]

[Updated on: Mon, 24 December 2018 04:59] by Moderator

Report message to a moderator

Re: Assign same Group id to rows [message #672192 is a reply to message #672191] Sat, 06 October 2018 07:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2966
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is not clear what results you expect.

SY.
Re: Assign same Group id to rows [message #672193 is a reply to message #672192] Sat, 06 October 2018 07:33 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
Required output shld be:

Gold_id rel_gold_id grp_id 
15	111	    1
15	2209	    1 
16	17	    1
16	2209	    1 
111	17	     1
17	24	    1

Here grp_id =1 depicts all belong yo same group. If We create tree all these will be inter-related to each other

[mod-edit: code tags added by bb]

[Updated on: Mon, 24 December 2018 05:01] by Moderator

Report message to a moderator

Re: Assign same Group id to rows [message #672195 is a reply to message #672193] Sat, 06 October 2018 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67235
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select 'Gold_id rel_gold_id grp_id
2 15 111 1
3 15 2209 1
4 16 17 1
5 16 2209 1
6 111 17 1
7 17 24 1
8 ' from dual;
Gold_id rel_gold_id grp_id
15 111 1
15 2209 1
16 17 1
16 2209 1
111 17 1
17 24 1

Re: Assign same Group id to rows [message #674007 is a reply to message #672191] Mon, 24 December 2018 05:52 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
-- table and data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
  2    (gold_id      NUMBER,
  3  	rel_gold_id  NUMBER,
  4  	grp_id	     NUMBER)
  5  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT ALL
  2  INTO test_tab (gold_id, rel_gold_id) VALUES (15, 111)
  3  INTO test_tab (gold_id, rel_gold_id) VALUES (15, 2209)
  4  INTO test_tab (gold_id, rel_gold_id) VALUES (16, 17)
  5  INTO test_tab (gold_id, rel_gold_id) VALUES (16, 2209)
  6  INTO test_tab (gold_id, rel_gold_id) VALUES (111, 17)
  7  INTO test_tab (gold_id, rel_gold_id) VALUES (17, 34)
  8  -- extra data for testing:
  9  INTO test_tab (gold_id, rel_gold_id) VALUES (100, 200)
 10  INTO test_tab (gold_id, rel_gold_id) VALUES (200, 300)
 11  INTO test_tab (gold_id, rel_gold_id) VALUES (300, 400)
 12  SELECT * FROM DUAL
 13  /

9 rows created.

-- query:
SCOTT@orcl_12.1.0.2.0> SELECT gold_id, rel_gold_id,
  2  	    DENSE_RANK () OVER (ORDER BY min_id) grp_id
  3  FROM   (SELECT gold_id, rel_gold_id,
  4  		    (SELECT MIN (LEAST (t2.gold_id, t2.rel_gold_id))
  5  		     FROM   test_tab t2
  6  		     START  WITH t2.gold_id = t1.gold_id AND t2.rel_gold_id = t1.rel_gold_id
  7  		     CONNECT BY NOCYCLE PRIOR gold_id = rel_gold_id OR PRIOR rel_gold_id = rel_gold_id) min_id
  8  	     FROM   test_tab t1)
  9  /

   GOLD_ID REL_GOLD_ID     GRP_ID
---------- ----------- ----------
        15         111          1
        15        2209          1
        16          17          1
        16        2209          1
       111          17          1
        17          34          1
       100         200          2
       200         300          2
       300         400          2

9 rows selected.
Previous Topic: Date for Sunday prior to given date
Next Topic: NVL not working in Cursor Subquery
Goto Forum:
  


Current Time: Wed Jul 08 18:39:50 CDT 2020