SQL query to to check uniqueness of a column value-CodePudding

Need a query to check(Select) if every combination of Value in colA and B has a unique value in col C. Please help. Thank you

This is the query I tried which doesn't give me the desired result:

select unique CONCAT(A,B),C 
from tab1 
group by CONCAT(A,B),C 
having count(distinct (CONCAT(A,B)))>1;

Sample table:

A       B   C
Tim     123 1
Jill    123 1
Jill    456 2
John    456 1
Jill    456 1

Here row 3 and 5 with same values in col A and B have different values in col C which is incorrect. I need to select those

CodePudding user response：

Something like this? (Sample data in lines #1 - 7; query begins at line #8):

SQL> with test (a, b, c) as
  2    (select 'Tim' , 123, 1 from dual union all
  3     select 'Jill', 123, 1 from dual union all
  4     select 'Jill', 456, 2 from dual union all
  5     select 'John', 456, 1 from dual union all
  6     select 'Jill', 456, 1 from dual
  7    )
  8  select t.*
  9  from test t
 10  where (t.a, t.b) in (select x.a, x.b
 11                       from test x
 12                       group by x.a, x.b
 13                       having count(distinct x.c) > 1
 14                      );

A             B          C
---- ---------- ----------
Jill        456          1
Jill        456          2

SQL>