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.

What isn't working with `SELECT Number, c1, c2, c3 FROM [table] GROUP BY Number, c1, c2, c3 HAVING Count(*) = 1`? — Fritz, Nov 18 '16 at 16:43
Turns out I can't read raw data. I didn't see that the 'c' values were different for records with the same number. — Fritz, Nov 18 '16 at 16:45

score 9 · Answer 1 · answered Nov 18 '16 at 16:43

9

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

answered Nov 18 '16 at 16:43

Martin Smith

438,706
87
741
845

score 8 · Accepted Answer · answered Nov 18 '16 at 16:42

8

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

answered Nov 18 '16 at 16:42

Derrick Moeller

4,808
2
22
48

score 3 · Answer 3 · answered Nov 18 '16 at 16:47

Take it in steps

What are the distinct numbers?

 SELECT number
 FROM Table
 GROUP BY number
 WHERE COUNT(number) = 1

Now select those

SELECT *
FROM TABLE 
WHERE number in (
 SELECT number
 FROM Table
 GROUP BY number
 WHERE COUNT(number) = 1
) x

or

SELECT number 
FROM Table
JOIN (
 SELECT distinct number
 FROM Table
 GROUP BY number
 WHERE COUNT(number) = 1
) x on Table.number = x.number

score 3 · Answer 4 · answered Nov 18 '16 at 16:52

create table TEST ( Number int, c1 char(1), c2 char(1),  c3 varchar(4));

insert into TEST
      select   325, 'A', 'K', 'NFW'
union select   325, 'U', 'G', 'GFD'
union select 32713, 'A', 'K', 'fgh'
union select  3271, 'U', 'G', 'ghad'
union select   327, 'A', 'G', 'yrg'
union select  3277, 'A', 'K', 'bfb'


select *
from TEST
inner join
(
    select Number
    from TEST
    group by Number
    having count(Number) = 1
) X on TEST.Number = X.Number

score 2 · Answer 5 · answered Nov 18 '16 at 16:42

2

 select * from table where
 number in (select number from table 
       group by number 
      having count(*) = 1)

answered Nov 18 '16 at 16:42

Utsav

7,914
2
17
38

score 2 · Answer 6 · answered Nov 18 '16 at 16:45

2

IF Table has lots of records, an exists could do it and be faster than an IN.

SELECT A.Number, A.c1,  A.c2,  A.c3
FROM Table A
WHERE exists (SELECT count(1), B.number 
              FROM table B 
              WHERE B.Number = A.Number
              GROUP BY B.Number
              HAVING count(1) = 1)

answered Nov 18 '16 at 16:45

xQbert

34,733
2
41
62

From what I've seen, in is faster on SQL Server and Exists is faster on DB2 -- however I haven't done side-by-side tests on the latest versions – Hogan Nov 18 '16 at 16:49
`In` vs `exists` depends on the data set being evaluated (Generally). `In` will be faster if the data set being considered is small like that of values found in a drop down. `Exists` will be faster if you're dealing with hundreds+ of records in part due to it's ability to early exit once a match is found. More on it found in [this stack QA](http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance) – xQbert Nov 18 '16 at 16:51

Select Only Those rows which occur exactly once

6 Answers6

Linked