How do I leave out ALL rows that have duplicates so NONE of the rows appear - meaning that if a number appear twice in a column the number won't appear in the output at all

Question

question

Mortymorth asked Apr 19, '19

How do I leave out ALL rows that have duplicates so NONE of the rows appear - meaning that if a number appear twice in a column the number won't appear in the output at all

I need an output that leaves out all hits with more than one instance.

From this example:

Pers ID Application number

777777 1

666666 1

666666 2

444444 1

I would like this output:

Pers ID Application number

777777 1

444444 1

I would like both to be able to pick out the instances with reference to the person's ID and the application number respectively. But I really can't hack it at all.

output duplicates

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-04-19T15:12:44Z

Kev Riley answered Apr 19, '19

Let's mock up your data

declare @YourTable table (PersID int, ApplicationNumber int)

insert into @YourTable (PersID, ApplicationNumber) select 777777,1
insert into @YourTable (PersID, ApplicationNumber) select 666666,1
insert into @YourTable (PersID, ApplicationNumber) select 666666,2
insert into @YourTable (PersID, ApplicationNumber) select 444444,1

Now let's find the duplicates

--first find the duplicates
select PersID 
from @YourTable 
group by PersID 
having count(*)>1

and use that to filter

--then use this data to exclude those rows
select *
from @YourTable
where PersID not in (select PersID 
		from @YourTable 
		group by PersID 
		having count(*)>1)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Apr 24, 2019 at 03:25 PM

@MortyMorth Once you understood the concept in @Key Riley answer, you might want to consider restating the query more succinctly, that is instead of searching for duplicates and then excluding them, just ignore them at once. Something like this should work as well:

select
    PersID, min(ApplicationNumber) ApplicationNumber
    from @YourTable
    group by PersID
    having count(1) = 1;

Please note that you need not be concerned about using the MIN aggregate, in fact you may use MAX instead as this will not change the result because including only those rows in the final result which have but one row per person, the value of the application number is also single, meaning that applying min or max to it will still return just that application number value.

This version in this comment is not guaranteed to be any faster than the original query in Kevan's answer, but this is something to consider because it might reduce the number of index scans from 2 to 1.

1 ·

question