This discussion is archived
1 Reply Latest reply: Jul 30, 2013 8:02 AM by BrendanP RSS

Group Similar Records together

1009115 Newbie
Currently Being Moderated

Version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 

The same Question has been asked yesterday :

Identify Different Groups in the Organization (Group the Same data together)

 

Help me in Identifying the groups(different Sectors) in the Organization

Each Group will have the same Group id if  We_org_id , We_addr_id or We_org_id , Ein Matches

In the below Example Row 1 and 2 Are linked with We_org_id , We_addr_id

and row 2 and 3 are linked with We_org_id , Ein  so all the three rows has the same groupid

 

 

Please give me some suggestions on how to proceed.... i will try to program.

 

Included Output Required.

Example

311    563          72-1500000    2

311    563          72-1500001    2

311    565          72-1500001    2

 

 

 

-- Table and inserts

    CREATE TABLE ORG_MISMATCH  
    (  
      WE_ORG_ID   NUMBER,  
      WE_ADDR_ID  NUMBER,  
      EIN         VARCHAR2(30 BYTE)  
    );  
    SET DEFINE OFF;  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 24303142, '31-1700059');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '39-1675361');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '31-1700059');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '26-0060245');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '72-1284709');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 121786868, '31-1700059');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 21053495, '72-1355929');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '73-1317052');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '56-2525845');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '72-1355929');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '84-1535762');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '91-2031795');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '84-1487943');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '91-2035844');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '84-1535753');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '72-1501788');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 56381251, '30-0137738');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 563, '72-1500001');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 565, '72-1500001');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '72-1355929');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 24303142, '31-1700059');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '31-1700059');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 121786868, '31-1700059');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '84-1535762');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '72-1501788');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '91-2031795');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '30-0137738');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '72-1284709');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '56-2525845');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '91-2035844');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 21053495, '72-1355929');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '39-1675361');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '84-1487943');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '84-1535753');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '26-0060245');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (999, 56381251, '73-1317052');  
    Insert into ORG_MISMATCH  
       (WE_ORG_ID, WE_ADDR_ID, EIN)  
    Values  
       (311, 563, '72-1500000');  
    COMMIT;  

Out put Required

 

WE_ORG_ID    WE_ADDR_ID    EIN    GROUPID

 

311    24303142    31-1700059    1

311    56381251    39-1675361    1

311    56381251    31-1700059    1

311    56381251    26-0060245    1

311    56381251    72-1284709    1

311    121786868    31-1700059    1

311    21053495    72-1355929    1

311    56381251    73-1317052    1

311    56381251    56-2525845    1

311    56381251    72-1355929    1

311    56381251    84-1535762    1

311    56381251    91-2031795    1

311    56381251    84-1487943    1

311    56381251    91-2035844    1

311    56381251    84-1535753    1

311    56381251    72-1501788    1

311    56381251    30-0137738    1

311    563       72-1500001    2

311    565       72-1500001    2

311    563       72-1500000    2

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points