Help with SQL

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.

Thanks.

Something like…

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
  )

Something of this nature?

select int_col, (select count(int_col) from mytable where int_col = t1.int_col) as c from mytable as t1 where c = 1

If it is still time, use something like below, then no need to exclude anything; all data are unique…:


CREATE TABLE t1(a INTEGER UNIQUE, b TEXT UNIQUE, ID INTEGER, PRIMARY KEY(ID));

Not tested, created in the web browser.

if it is a cleaning-up operation you can copy rows from non unique into a table mentioned by emile.

This won’t work for me as the data has no guarantee of being unique.

Here is what I got elsewhere.
select * from table group by number having count(number) = 1
works fine.

Just be careful.

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.