Assign same Group id to rows [message #672191] |
Sat, 06 October 2018 07:06 |
|
purnima1
Messages: 79 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 #674007 is a reply to message #672191] |
Mon, 24 December 2018 05:52 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|