Morpheus Morpheus - 15 days ago 5
SQL Question

Select Only Those rows which occur exactly once

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

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
Comments