Find duplicate in a table
<p>I have a table that contains duplicate.
The way to identify a duplicate is
 - the key should be in the same group(1,2,3 or 4)
 - the p should be the same
 - P is an id that say this keys are the same
A key could match multiple times in the same group only.</p>

<p>Let's say we have this sample below :</p>

<pre><code>key,p,group
1,1,1
33,1,1
5,1,1
5,2,1
4,2,1
4,15,1
8,4,1
10,5,1
15,6,1
21,15,1
78,7,1
79,8,2
80,8,2
81,9,3
82,9,3
85,10,4
90,11,1
91,11,1
73,12,1
</code></pre>

<p>The output should be :</p>

<pre><code>key,p,group
1,999,1
5,999,1
4,999,1
21,999,1
33,999,1
8,4,1
10,5,1
15,6,1
78,7,1
79,111,2
80,111,2
81,666,3
82,666,3
85,10,4
90,222,1
91,222,1
73,12,1
</code></pre>

<p>1,5,4,21 and 33 have the same value for p(999, this number is just a new id for grouping the duplicate together),
because they are in the same group (group=1) and 1,5 and 33  match (p=1) , 5 and 4 match (p=2) , 4 and  21 match (p=15)</p>

<p>For 90,91 even they are in the group 1, they match only together because they are not link(cross) with the other key in this group.</p>

<p>79 and 80 are in the same group (group=2)</p>

<p>8 keep p = 4 because he does not match with other keys in the group = 1.</p>

<p>and so forth ...
I'm looking for a way to do it in SQL(Oracle) or an algorithm ...</p>

<p>Actually,
it is not working.
If you have this in input :</p>

<pre><code>key,p,group
55,9,6
56,10,6
56,11,6
58,9,6
58,11,6
</code></pre>

<p>The output will be</p>

<pre><code>key,p,group
55,9,6
56,9,6
58,9,6
56,10,6
58,10,6
</code></pre>

<p>or I need :</p>

<pre><code>key,p,group
55,9,6
56,9,6
58,9,6
56,9,6
58,9,6
</code></pre>

<p>Thanks for help</p>