Search code examples
sqlcountsubquerywhere-clause

SQL Select ALL ROWS but only if column value is unique

If I do the following:

select * from myTable

I get:

Column 1  | Column 2
---------------------
1         | Create
1         | Delete
2         | Create
3         | Create
4         | Create
4         | Delete
5         | Create

I want to perform a select statement where I only pull the rows that have a unique number for column 1

In otherwords I am looking for this result:

Column 1  | Column 2
---------------------
2         | Create
3         | Create
5         | Create

Not sure exactly how to pull this off with only one statement or if there even is a way to do that. Thanks!

Solution

  • If there are not duplicate (column1, column2) tuples, one option is not exists:

    select t.*
from mytable t
where not exists (
    select 1 from mytable t1 where t1.column1 = t.column1 and t1.column2 <> t.column2
)