SQL & PL/SQL

Hi,

My version is 11.2.0.4 Standard Edition.

Consider the following data:

WITH sample_data(prd, c, s, q)

AS (select '1001088', '0001', '38', 34 from dual union all

select '1001088', '0001', '40', 44 from dual union all

select '1001088', '0001', '42', 87 from dual union all

select '1001088', '0001', '44', 104 from dual union all

select '1001088', '0001', '46', 108 from dual union all

select '1001088', '0001', '48', 111 from dual union all

select '1001088', '0001', '50', 97 from dual union all

select '1001088', '0001', '52', 97 from dual union all 

select '1001088', 'ABCD', '38', 19 from dual union all 

select '1001088', 'ABCD', '40', 73 from dual union all 

select '1001088', 'ABCD', '42', 115 from dual union all

select '1001088', 'ABCD', '44', 116 from dual union all

select '1001088', 'ABCD', '46', 138 from dual union all

select '1001088', 'ABCD', '48', 121 from dual union all

select '1001088', 'ABCD', '50', 81 from dual union all 

select '1001088', 'ABCD', '52', 73 from dual union all 

select '1001088', '0032', '38', 0 from dual union all        

select '1001088', '0032', '40', 73 from dual union all      

select '1001088', '0032', '42', 115 from dual union all

select '1001088', '0032', '44', 11 from dual union all      

select '1001088', '0032', '46', 111 from dual union all   

select '1001088', '0032', '48', 121 from dual union all

select '1001088', '0032', '50', 82 from dual union all

select '1001088', '0032', '52', 75 from dual union all      

select '1001088', 'EFGH', '42', 111 from dual union all

select '1001088', 'EFGH', '44', 15 from dual union all 

select '1001088', 'EFGH', '46', 13 from dual union all 

select '1001088', 'EFGH', '48', 12 from dual union all 

select '1001088', 'EFGH', '50', 81 from dual union all 

select '1001088', 'EFGH', '52', 73 from dual

)

select * from sample_data;

PRD     C    S         Q    RESULT

-------------------------------------

1001088 0001 38         34

1001088 0001 40         44

1001088 0001 42         87

1001088 0001 44        104

1001088 0001 46        108

1001088 0001 48        111

1001088 0001 50         97

1001088 0001 52         97

1001088 ABCD 38         19

1001088 ABCD 40         73

1001088 ABCD 42        115

1001088 ABCD 44        116

1001088 ABCD 46        138

1001088 ABCD 48        121

1001088 ABCD 50         81

1001088 ABCD 52         73

1001088 0032 38          0  138

1001088 0032 40         73  138

1001088 0032 42        115  138

1001088 0032 44         11  138

1001088 0032 46        111  138

1001088 0032 48        121  138

1001088 0032 50         82  138

1001088 0032 52         75  138

1001088 EFGH 42        111

1001088 EFGH 44         15

1001088 EFGH 46         13

1001088 EFGH 48         12

1001088 EFGH 50         81

1001088 EFGH 52         73

For a given PRD, I have to check for each C partition if there's a row with Q = 0. This is the case for C = '0032' (row with S = 38). Identifying such a group is easy.

Now for such a group, I have to find a similar group with the same values for S. There must be a match for each S value.

Here the groups C = '0001' and C = 'ABCD' are suitable because each value of S in group '0032' is found in both these groups.

Group C = 'EFGH' is not because not all values of S from group '0032' are present.

Finally, the similar group with the highest Q will be selected, so in RESULT I will place that max Q, that is, 138 of group 'ABCD'.

My question is, is there an easy way to identify the similar groups without self-joins?

Thanks