SQL query to to check uniqueness of a column value

Question

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

please edit the question and show sample table data and expected output — OldProgrammer
– OldProgrammer, Commented Sep 29, 2021 at 19:45

Littlefoot · Accepted Answer · 2021-09-29 20:07:27Z

1

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>

answered Sep 29, 2021 at 20:07

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query to to check uniqueness of a column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related