CODEWITHSUNDEEP
sqloracle-database
Morticia A. Addams
Morticia A. Addams

Reputation: 383

SQL ORACLE - get group of IDs between has path

I have table like this:

ID1 | ID2
==========
A   | B
C   | B
A   | F
G   | B
G   | E
J   | B
X   | Y
Z   | Y

I have to write SQL SELECT that find(marks) separate groups. In this example I have two groups: A,B,C,F,G,E,J and X,Y,Z. It is like all that have path(without direction) between them(like Family relation).There is no matter it will be string(with LISTAGG or with path (CONNECT BY or CYCLE) for example) or new column with group identifiers like

ID1 | ID2 | GROUP
=================
A   | B   | 1
C   | B   | 1
A   | F   | 1
G   | B   | 1
G   | E   | 1
J   | B   | 1
X   | Y   | 2
Z   | Y   | 2

If there is one more row with [A,Z] then all distinct letters are one group. I think its have to something with CONNECT BY or CYCLE but I don't know how it have to looks like.

Upvotes: 0

Views: 105

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

I think You could use connect_by_root here:

select id1, listagg(root) within group (order by root) list
  from (select distinct id1, root
          from (select t.*, connect_by_root(id1) root
                  from (select id1, id2 from t union select id2, id1 from t) t
                  connect by nocycle prior id1 = id2))
  group by id1

... gives us:

A   ABCEFGJ
B   ABCEFGJ
C   ABCEFGJ
E   ABCEFGJ
F   ABCEFGJ
G   ABCEFGJ
J   ABCEFGJ
X   XYZ
Y   XYZ
Z   XYZ

Rest is simple, use dense_rank() and join with original table:

with t(id1, id2) as (
    select 'A', 'B' from dual union all
    select 'C', 'B' from dual union all
    select 'A', 'F' from dual union all
    select 'G', 'B' from dual union all
    select 'G', 'E' from dual union all
    select 'J', 'B' from dual union all
    select 'X', 'Y' from dual union all
    select 'Z', 'Y' from dual),
q as (
    select id1, listagg(root) within group (order by root) list
      from (select distinct id1, root
              from (select t.*, connect_by_root(id1) root
                      from (select id1, id2 from t union select id2, id1 from t) t
                      connect by nocycle prior id1 = id2))
      group by id1)
select id1, id2, dense_rank() over (order by list) grp
  from t join q using (id1)

Result:

ID1 ID2        GRP
--- --- ----------
A   F            1
A   B            1
C   B            1
G   E            1
J   B            1
G   B            1
X   Y            2
Z   Y            2
8 rows selected

Upvotes: 1

MT0
MT0

Reputation: 168051

Adapted from my answer here:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE ids ( ID1, ID2 ) AS
  SELECT 'A', 'B' FROM DUAL UNION ALL
  SELECT 'C', 'B' FROM DUAL UNION ALL
  SELECT 'A', 'F' FROM DUAL UNION ALL
  SELECT 'G', 'B' FROM DUAL UNION ALL
  SELECT 'G', 'E' FROM DUAL UNION ALL
  SELECT 'J', 'B' FROM DUAL UNION ALL
  SELECT 'X', 'Y' FROM DUAL UNION ALL
  SELECT 'Z', 'Y' FROM DUAL;

Query 1:

WITH indexed_ids ( id, id1, id2 ) AS (
  SELECT ROWNUM, id1, id2 FROM ids
),
grouped_ids ( id, id1, id2, min_id ) AS (
  SELECT i.*,
         LEAST(
           MIN( id ) OVER ( PARTITION BY id1 ),
           MIN( id ) OVER ( PARTITION BY id2 )
         ) AS min_id
  FROM   indexed_ids i
)
SELECT id, id1, id2,
       MIN( "GROUP" ) AS "GROUP"
FROM   (
  SELECT id, id1, id2,
         CONNECT_BY_ROOT( id ) AS "GROUP"
  FROM   grouped_ids g
  START WITH id = min_id
  CONNECT BY NOCYCLE ( PRIOR id1 IN ( id1, id2 ) OR PRIOR id2 IN ( id1, id2 ) )
)
GROUP BY id, id1, id2
ORDER BY id

Results:

| ID | ID1 | ID2 | GROUP |
|----|-----|-----|-------|
|  1 |   A |   B |     1 |
|  2 |   C |   B |     1 |
|  3 |   A |   F |     1 |
|  4 |   G |   B |     1 |
|  5 |   G |   E |     1 |
|  6 |   J |   B |     1 |
|  7 |   X |   Y |     7 |
|  8 |   Z |   Y |     7 |

The GROUP column identifies the minimum ID of the group. If you want a sequential GROUP then you can use the DENSE_RANK() analytic function.

Upvotes: 1

Related Questions