Ignore duplicates?

Question

Column1
1
1
2
3
4
4
4
5

Is there a way to query that column and only return

2
3
5

If I use Select Distinct it will return

How can I write a select statement that if there is a duplicity not to select just one but to completely ignore both/all rows with that value? But this is a single column being selected in a table on the right side of a Left Join

score 3 · Accepted Answer · answered Mar 14 '13 at 21:01

3

I think this is what you want:

SELECT a.column1 
FROM   (SELECT column1, 
               Count(*) cnt 
        FROM   tbl 
        GROUP  BY column1 
        HAVING Count(*) = 1) a

Result

| COLUMN1 |
-----------
|       2 |
|       3 |
|       5 |

See the demo

answered Mar 14 '13 at 21:01

Kermit

33,827
13
85
121

I think you mean having count(*) = 1 – Keith John Hutchison Mar 14 '13 at 21:02
Updated question It posted without the full question. – user2140261 Mar 14 '13 at 21:10
@user2140261 You would use `NOT IN`. See [the docs](http://msdn.microsoft.com/en-us/library/ms177682.aspx). Here's a [demo](http://sqlfiddle.com/#!3/9c8b2/1) – Kermit Mar 14 '13 at 21:16
You have answered this to the best of your ability based on the question I asked I have accepted this as the answer. And proceeded to ask the question I meant to ask in another question here: http://stackoverflow.com/questions/15420520/left-join-where-the-right-table-has-having-count-1 – user2140261 Mar 14 '13 at 21:28

score 2 · Answer 2 · answered Mar 14 '13 at 21:01

2

SELECT Column1
FROM YourTable
GROUP By Column1
HAVING COUNT(*) = 1

Sql Fiddle

answered Mar 14 '13 at 21:01

Michael Fredrickson

36,839
5
92
109

Ignore duplicates?

2 Answers2

Sql Fiddle