SQL select where repeating, but distinct on another column.

Question

I have the following table:

uid | key | email
-----------------
1   |  1  | [email protected]
2   |  2  | [email protected]
3   |  3  | [email protected]
4   |  4  | [email protected]
5   |  4  | [email protected]
6   |  4  | [email protected]
7   |  6  | [email protected]
8   |  7  | [email protected]

I'd like to grab all of the rows with distinct key but repeating email values

the result should look like:

uid | key | email
-----------------
1   |  1  | [email protected]
2   |  2  | [email protected]
3   |  3  | [email protected]

Answer 1

SELECT MIN(uid) as uid, key, email
FROM Keys k INNER JOIN 
    (SELECT email FROM KEYS GROUP by email HAVING COUNT(email) > 1 ) k2
    ON k.email = k2.email
GROUP BY key, email
HAVING COUNT(key) = 1

Answer 2

SELECT * FROM table
WHERE email NOT IN
(
SELECT email 
FROM table GROUP BY email
HAVING COUNT(email) <= 1)
AND key IN
(
SELECT key
FROM table
GROUP BY key
HAVING COUNT(key) = 1 
)