Skip to content
Advertisement

How do I write a case statement to return duplicate records?

I have a table of names and addresses and want to return the full list of names and addresses with an extra column that identifies if the address is a duplicate or unique address. There are several duplicate address entries that I can see when I run a more simple statement —

or

but I need to return the full table with columns –

If I use the below case statement, the group condition impacts the PersonAddressLine1 count result. It shows the addresses all as unique.

Advertisement

Answer

you can use window function COUNT(*) OVER (PARTITION BY PersonAddressLine1) to count the no of duplicates

what your current query is doing right now is not only checking for PersonAddressLine1 but all of the following PersonAddressLine1, PersonID, PersonName, CompanyID, CompanyName

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement