Select Only Those rows which occur exactly once

Question

I have the following data:

    Number  c1  c2  c3
    325     A   K   NFW
    325     U   G   GFD
    32713   A   K   fgh
    3271    U   G   ghad
    327     A   G   yrg
    3277    A   K   bfb

I want to not select those rows which are not unique. i.e I want only those rows which have a distinct "Number" column. My result should Ideally look like:

    Number  c1  c2  c3
    32713   A   K   fgh
    3271    U   G   ghad
    327     A   G   yrg
    3277    A   K   bfb

I have written the following code but it is not exactly working:

SELECT * from [table] GROUP BY [all columns ] HAVING Count(*) = 1

Any suggestion to get he required result will be appreciated.

Answer 1

You could also use a windowed aggregate

WITH T
     AS (SELECT *,
                COUNT(*) OVER (PARTITION BY Number) AS C
         FROM   [table])
SELECT Number,
       c1,
       c2,
       c3
FROM   T
WHERE  C = 1 

Answer 2

You were nearly there, simply group on Number.

SELECT Number, MIN(c1) AS c1, MIN(c2) AS c2, MIN(c3) AS c3
FROM [table]
GROUP BY Number
HAVING COUNT(*) = 1