Oracle SQL - How to select duplicate records on 2 columns but where 3 column not equal-CodePudding

I would like to return rows from a table where col1 and col2 are the same (duplicates) but col3 NOT the same

Col1	Col2	Col3	Col4
1	a	x	q
1	a	y	w
2	b	x	e
2	b	x	r
2	b	y	t
3	c	x	y
3	c	y	u
3	d	z	i

I was working with query using OVER PARTITION to get count of duplicates then select those but I cannot find way to add in the 3rd column not being equal. I have this so far. Will I somehow have to do another CTE for the third column?

with dup_count AS (  
    select o.*, count(*) over (partition by col1, col2) c
    from table o)  
select * from dup_count where c > 1

What I would like to return for the above table results is below.
First 2 Columns duplicates 3rd not equal, col4 or other columns don't matter.

Col1	Col2	Col3	Col4
1	a	x	q
1	a	y	w
2	b	x	e
2	b	y	t
3	c	x	y
3	c	y	u

CodePudding user response：

As col4 and other columns don't matter, take any (e.g. min); then you could - with sample data:

SQL> with test (col1, col2, col3, col4) as
  2    (select 1, 'a', 'x', 'q' from dual union all
  3     select 1, 'a', 'y', 'w' from dual union all
  4     select 2, 'b', 'x', 'e' from dual union all
  5     select 2, 'b', 'x', 'r' from dual union all
  6     select 2, 'b', 'y', 't' from dual union all
  7     select 3, 'c', 'x', 'y' from dual union all
  8     select 3, 'c', 'y', 'u' from dual union all
  9     select 3, 'd', 'z', 'i' from dual
 10    ),

use such a query:

 11  temp as
 12    (select col1, col2, col3, min(col4) col4,
 13       dense_rank() over (partition by col1, col2 order by col3) rnk
 14     from test
 15     group by col1, col2, col3
 16    )
 17  select col1, col2, col3, col4
 18  from temp
 19  where (col1, col2) in (select col1, col2
 20                         from temp
 21                         group by col1, col2
 22                         having max(rnk) > 1)
 23  order by col1, col2, col3, col4;

  COL1 COL2  COL3  COL4
------ ----- ----- -----
     1 a     x     q
     1 a     y     w
     2 b     x     e
     2 b     y     t
     3 c     x     y
     3 c     y     u

6 rows selected.

SQL>