I’m having a bit of trouble devising SQL for the following problem. I have a table with an integer column and a text column. No value in either column is guaranteed to be unique. What I’m trying to do is come up with a SQL statement that will return only the rows where the value in the number field occurs only once in the table.
SELECT *
FROM my_table AS t1
WHERE
num_col IS NOT NULL
AND NOT EXISTS (
SELECT *
FROM my_table AS t2
WHERE
t2.id <> t1.id
AND t2.num_col = t1.num_col
)
This formula gets the numbers that are not duplicated. Numbers duplicated do not appear in this query.
You can do the following to check those records with duplicate numbers:
SELECT * FROM table GROUP BY number HAVING COUNT (number)> 1
I imagine that your table intentionally has duplicate numbers that are part of a relationship, so there are few that are not. If this is the case, your formula suits you, if it is not, you will decrease the quality of your query.