Identify records with duplicate and unique values

Question

I am looking for duplicate addresses on records where a foreign key differs. This gives me the duplicates:

select quoteID, insuredID, Address1, PolicyID
from quote
where 
address1 in(select address1 from quote group by address1 having count(address1) > 1)
and datalength(address1) <> 0
order by address1

I want to filter out all records where the insuredID is the same. I really need to keep the quoteID in the results so I can investigate problem accounts. I am using SQL Server 2008.

possible duplicate of [Select statement to find duplicates on certain fields](http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields) — Marc B, Jun 18 '15 at 20:15

score 0 · Answer 1 · answered Jun 18 '15 at 20:26

0

Use window functions. If I understand the logic you want:

select q.*
from (select q.*,
             min(insuredId) over (partition by address1) as minii,
             max(insuredId) over (partition by address1) as maxii
      from quote
      where datalength(address1) <> 0
     ) q
where minii <> maxii;

If I have the columns wrong, then something similar is probably what you want.

I assume you have a good reason for using datalength() rather than just len().

answered Jun 18 '15 at 20:26

Gordon Linoff

1,242,037
58
646
786

I am new to SQL, found how to eliminate blanks on this site. I just swapped out datalength with len and that works too - will have to read up on the difference. I tried a slightly modified version of your suggestion and it puts me closer... Thanks - I'll continue to refine that amd mark as answered if I get exactly what I want. – John Danner Jun 18 '15 at 20:54

score 0 · Answer 2 · answered Jun 18 '15 at 23:28

If I understand you correctly you want find only those record has the same address but with different insuredId.

The following query should do it, firstly it grab all duplicate address record as you did, then by join it to pull out only records that with those address. Then group by the address and insuredId together to grab only record count is 1, anything greater than 1 means they share the same insuredId and address which is not you want.

select quoteID, insuredID, Address1, PolicyID
from quote
    inner join (select Address1, insuredID
    from quote
        inner join (select address1 from quote where address1 IS NOT NULL group by address1 having count(address1) > 1) T1 on quote.address1 = T1.address1
    group by insuredID, Address1
    having count(address1) = 1) T2  on quote.address1 = T2.address1 and quote.insuredID = T2.insuredID
order by address1

Identify records with duplicate and unique values

2 Answers2