sql query to find non-mapping column values

Question

Difficult to put down in words, so assuming this example table:

| id | col1 | col2 | 
--------------------
| 1  |  aa  |  12  |
| 2  |  aa  |  12  |
| 3  |  bb  |  13  |
| 4  |  cc  |  13  |

I would like a query which selects rows 3 & 4 or even just the value 13

So something like checking this assumption: "all values of col2 which are the same should map one value of col1"

I've been checking by doing a 'group by' and row count for each column in separate queries and comparing, but it would be nice to be able to pick out offending rows

A query or pl/sql procedure would be fine

Answer 1

If what you want is col2 based on unique col1, this is it:

SELECT col2 FROM [table] GROUP BY col1 HAVING count(id) = 1;

If you want col2 based on a unique col1 and col2 value, the following should work:

SELECT col2 FROM [table] GROUP BY col1, col2 HAVING count(id) = 1;

Answer 2

To get just "13":

select col2
from mytable
group by col2 having count(distinct col1) > 1;

To get the rows:

select * from mytable where col2 in
( select col2
  from mytable
  group by col2 having count(distinct col1) > 1
);