Apply the distinct on 2 fields and also fetch the unique data for each columns

Question

According to some weird requirement, i need to select the record where all the output values in both the columns should be unique.

Input looks like this:

col1   col2
1       x
1       y
2       x
2       y
3       x
3       y
3       z

Expected Output is:

col1  col2
1     x
2     y
3     z

or

col1  col2
1     y
2     x
3     z

I tried applying the distinct on 2 fields but that returns all the records as overall they are distinct on both the fields. What we want to do is that if any value is present in the col1, then it cannot be repeated in the col2.

Please let me know if this is even possible and if yes, how to go about it.

Answer 1

Great problem! Armunin has picked up on the deeper structural issue here, this is a recursive enumerable problem description and can only be resolved with a recursive solution - base relational operators (join/union/etc) are not going to get you there. As Armunin cited, one approach is to bring out the PL/SQL, and though I haven't checked it in detail, I'd assume the PL/SQL code will work just fine. However, Oracle is kind enough to support recursive SQL, through which we can build the solution in just SQL:

-- Note - this SQL will generate every solution - you will need to filter for SOLUTION_NUMBER=1 at the end

with t as (
select 1 col1, 'x' col2 from dual union all
select 1 col1, 'y' col2 from dual union all
select 2 col1, 'x' col2 from dual union all
select 2 col1, 'y' col2 from dual union all
select 3 col1, 'x' col2 from dual union all
select 3 col1, 'y' col2 from dual union all
select 3 col1, 'z' col2 from dual
), 
t0 as 
    (select t.*, 
            row_number() over (order by col1) id, 
            dense_rank() over (order by col2) c2_rnk 
     from t),
-- recursive step...
t1 (c2_rnk,ids, str) as
    (-- base row
     select c2_rnk, '('||id||')' ids, '('||col1||')' str 
     from   t0 
     where  c2_rnk=1
     union all
     -- induction
     select t0.c2_rnk, ids||'('||t0.id||')' ids, str||','||'('||t0.col1||')' 
     from   t1, t0 
     where  t0.c2_rnk = t1.c2_rnk+1 
            and instr(t1.str,'('||t0.col1||')') =0
    ),
t2 as 
    (select t1.*, 
            rownum solution_number 
     from   t1 
     where  c2_rnk = (select max(c2_rnk) from t1)
    )
select  solution_number, col1, col2 
from    t0, t2 
where   instr(t2.ids,'('||t0.id||')') <> 0
order by 1,2,3


SOLUTION_NUMBER       COL1    COL2 
1                     1       x    
1                     2       y    
1                     3       z    
2                     1       y    
2                     2       x    
2                     3       z    

Answer 2

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE tbl ( col1, col2 ) AS
          SELECT 1, 'x' FROM DUAL
UNION ALL SELECT 1, 'y' FROM DUAL
UNION ALL SELECT 2, 'x' FROM DUAL
UNION ALL SELECT 2, 'y' FROM DUAL
UNION ALL SELECT 3, 'x' FROM DUAL
UNION ALL SELECT 3, 'y' FROM DUAL
UNION ALL SELECT 4, 'z' FROM DUAL;

Query 1:

WITH c1 AS (
  SELECT  DISTINCT
          col1,
          DENSE_RANK() OVER (ORDER BY col1) AS rank
  FROM    tbl
),
c2 AS (
  SELECT  DISTINCT
          col2,
          DENSE_RANK() OVER (ORDER BY col2) AS rank
  FROM    tbl
)
SELECT c1.col1,
       c2.col2
FROM   c1
       FULL OUTER JOIN c2
       ON ( c1.rank = c2.rank)
ORDER BY COALESCE( c1.rank, c2.rank)

Results:

| COL1 |   COL2 |
|------|--------|
|    1 |      x |
|    2 |      y |
|    3 |      z |
|    4 | (null) |

And to address the additional requirement:

What we want to do is that if any value is present in the col1, then it cannot be repeated in the col2.

Query 2:

WITH c1 AS (
  SELECT  DISTINCT
          col1,
          DENSE_RANK() OVER (ORDER BY col1) AS rank
  FROM    tbl
),
c2 AS (
  SELECT  DISTINCT
          col2,
          DENSE_RANK() OVER (ORDER BY col2) AS rank
  FROM    tbl
  WHERE   col2 NOT IN ( SELECT TO_CHAR( col1 ) FROM c1 )
)
SELECT c1.col1,
       c2.col2
FROM   c1
       FULL OUTER JOIN c2
       ON ( c1.rank = c2.rank)
ORDER BY COALESCE( c1.rank, c2.rank)

Answer 3

You can use a full outer join to merge two numbered lists together:

SELECT  col1, col2
FROM  ( SELECT col1, ROW_NUMBER() OVER ( ORDER BY col1 ) col1_num
        FROM   your_table
        GROUP BY col1 )
  FULL JOIN 
      ( SELECT col2, ROW_NUMBER() OVER ( ORDER BY col2 ) col2_num
        FROM   your_table
        GROUP BY col2 )
  ON  col1_num = col2_num

Change ORDER BY if you require a different order and use ORDER BY NULL if you're happy to let Oracle decide.

Answer 4

My suggestion is something like this:

begin
    EXECUTE IMMEDIATE 'CREATE global TEMPORARY TABLE tmp(col1 NUMBER, col2 VARCHAR2(50))';
end;
/
DECLARE
    cur_print sys_refcursor;
    col1 NUMBER;
    col2 VARCHAR(50);
    CURSOR cur_dist
    IS
        SELECT DISTINCT
            col1
        FROM
            ttable;
    filtered sys_refcursor;
BEGIN
    FOR rec IN cur_dist
    LOOP
        INSERT INTO tmp
        SELECT
            col1,
            col2
        FROM
            ttable t1
        WHERE
            t1.col1         = rec.col1
        AND t1.col2 NOT IN
            (
                SELECT
                    tmp.col2
                FROM
                    tmp
            )
        AND t1.col1 NOT IN
            (
                SELECT
                    tmp.col1
                FROM
                    tmp
            )
        AND ROWNUM = 1;
    END LOOP;

    FOR rec in (select col1, col2 from tmp) LOOP
        DBMS_OUTPUT.PUT_LINE('col1: ' || rec.col1 || '|| col2: ' || rec.col2);
    END LOOP;

    EXECUTE IMMEDIATE 'DROP TABLE tmp';
END;
/

May still need some refining, I am especially not happy with the ROWNUM = 1 part.

Answer 5

What would be the result if another row of col1 value as 1 and col2 value as xx ?

A single row is better in this case:

SELECT DISTINCT TO_CHAR(col1) FROM your_table
UNION ALL
SELECT DISTINCT col2 FROM your_table;