SELECT Columns WHERE one column Is Distinct?

Question

Can anyone help me with the query ?

I've tried the following but it comes up with an error

SELECT Column1, Column2, Column3 FROM Table WHERE [Column1] NOT IN 
(SELECT [Column1] FROM Table GROUP BY [Column1] HAVING COUNT([Column]) > 1)

Invalid MEMO, OLE, or Hyperlink Object in subquery [Column1].

score 4 · Accepted Answer · answered Nov 14 '12 at 15:23

4

Use Group By with Having clause:

SELECT Column1, MIN(Column2)AS Column2, MIN(Column3)AS Column3
FROM dbo.Table
GROUP BY Column1
HAVING ( COUNT(Column1) = 1 )

Should work since there's only one row per "group".

answered Nov 14 '12 at 15:23

Tim Schmelter

450,073
74
686
939

Having the rest of the Columns in MIN() Function doesn't give me the exception. Thank you. – Pantelis Nov 14 '12 at 18:22

score 1 · Answer 2 · answered Nov 14 '12 at 16:03

Your original query should work you just had column instead of column1.

 SELECT Column1, Column2, Column3 FROM TableName WHERE [Column1] NOT IN 
(SELECT [Column1] FROM TableName GROUP BY [Column1] HAVING COUNT(Column1) > 1)

see: http://sqlfiddle.com/#!3/d99a8/5/0

score 0 · Answer 3 · answered Nov 14 '12 at 15:29

As far as i get , you need all data where [Column1] is unique (appear just one time)

DECLARE @x TABLE (col1 INT, col2 INT, col3 INT)

INSERT INTO @x
        ( [col1], [col2], [col3] )
VALUES  ( 1, 2, 3  )
        ,( 1, 4, 5 )
        ,( 2, 6, 7 )

SELECT * FROM @x

SELECT col1, col2 , col3 FROM @x
WHERE col1 NOT IN 
    ( SELECT [col1]  FROM @x GROUP BY [col1] HAVING  COUNT(*) > 1 )

SELECT Columns WHERE one column Is Distinct?

3 Answers3