Q:

finding duplicates rows for specified columns

I have data like below where i need to find the ids which are having same values(count of values should also same).
Here id 1 and 3 are having same values with different id so i want these 2 ids as out put.
Id is 4 is also having same links but it is also having different value 333 so i don't want that.
id  value
1   111
1   222

2   222
2   333

3   111
3   222

4   111
4   222
4   333

5       111
5       444

I tried using exists query but it is also returning 5 which is wrong.I am using oracle 11G schema.
Output expecting is something like 
giving some number to the same ids to identify the duplicates.
so for 
1   111   1
1   222   1
3       111   1
3       111   1

A:

You need SET operations for this. One idea for testing set equality is this: A MINUS B EQUALS B MINUS A EQUALS EMPTY SET.
Here is how you can do it with PL/SQL.
DECLARE
  v_count_1 number;
  v_count_2 number;
BEGIN
  FOR r IN (SELECT DISTINCT id FROM my_table) LOOP
    FOR r_inner in (SELECT DISTINCT id FROM my_table WHERE id > r.id) LOOP
      SELECT count(*)
      INTO v_count_1
      FROM (SELECT value FROM my_table WHERE id = r.id
            MINUS
            SELECT value FROM my_table WHERE id = r_inner.id);

      SELECT count(*)
      INTO v_count_2
      FROM (SELECT value FROM my_table WHERE id = r_inner.id
            MINUS
            SELECT value FROM my_table WHERE id = r.id);

      IF v_count_1 = 0 AND v_count_2 = 0 THEN
        dbms_output.put_line('duplicate IDs: '||r.id||' '||r_inner.id);
      END IF;
    END LOOP;
  END LOOP;
END;
/

