removing duplicates

Question

removing duplicates

Steve Jones - SSC Editor

SSC Guru

Points: 736362
More actions
September 4, 2013 at 10:37 am

#277650

Show how to find duplicates based on another field. This can be the primary key, but perhaps not. Perhaps it's a timestamp of some sort.
Show how to query to check and then construct a delete statement based on the select.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

You mean use another existing field to differentiate between duplicates in the rest of the data?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 2

Yes, as you could choose a primary key, or if one doesn't exist (or isnt' appropriate) another one. This one is really to teach how someone that doesn't know how to remove a duplicate can do so.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Does the removing the duplicates job include adding a constraint to make sure the problem doesn't recur?

Tom

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 4

Worth mentioning, but not necessary. That's not always a possibility.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

L' Eomot Inversé (9/7/2013)
Does the removing the duplicates job include adding a constraint to make sure the problem doesn't recur?

I agree that it probably should at least have an honorable mention... except maybe on staging tables that take in data from 3rd party sources.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Meierruth SSChampion Points: 10051 More actions · Answer 6

Steve,

It is mentioned at

http://www.sqlservercentral.com/Forums/Topic793765-145-1.aspx

It uses CTE with delete. Very neat.

create table t(a varchar(1))

insert into t values('b')

insert into t values('a')

insert into t values('b')

insert into t values('a')

with

dup as

(

select a,row_number() over(partition by a order by a) rn

from t

)

delete from dup where rn>1

In Oracle this would be pretty straight forward via the use of the pseudo rowid column that all tables have.

This references a physical (and thus unique) address of each row.

At this link

http://www.codeproject.com/Articles/159785/Physical-location-of-a-row-in-SQL-Server

there is an interesting discussion of undocumented %%physloc%% and %%lockres%% columns starting with SS2008R2.

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 736362

January 31, 2014 at 9:27 am

#1685112

Bump

Looking for a writeup here as an article.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Would this be of an interest?

Data de-duplication using 2012 window functions.

Simple cases;

1. A set with no surrogate key defined

2. A set with a surrogate key, related records must be updated

Complex cases;

1. Retaining the first value by any chosen sequence

2. Retaining the first good value by any chosen sequence and pattern

3. Retaining the last value by any chosen sequence

4. Retaining the last good value by any chosen sequence and pattern

5. Retaining the most dense value by any chosen pattern

6. Retaining the least dense value by any chosen pattern

Business rule application (possibly a separate article);

1. Rule controlled matching

2. Rule controlled de-duplication

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 9

These would be good. We'd be looking for 2-3 per article to keep this relatively short.

a4apple SSCrazy Points: 2620 More actions · Answer 10

Steve, here is a step by step article I wrote on how to remove duplicates from a table in SQL Server. Still waiting for approval for the previous submitted articles, so posting the original link for my website for now. see if this helps.

http://sqlsaga.com/sql-server/how-to-remove-duplicates-from-a-table-in-sql-server/

[/url]

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 11

Eirikur Eiriksson (2/16/2014)
Would this be of an interest?
Data de-duplication using 2012 window functions.
Simple cases;
1. A set with no surrogate key defined
2. A set with a surrogate key, related records must be updated
Complex cases;
1. Retaining the first value by any chosen sequence
2. Retaining the first good value by any chosen sequence and pattern
3. Retaining the last value by any chosen sequence
4. Retaining the last good value by any chosen sequence and pattern
5. Retaining the most dense value by any chosen pattern
6. Retaining the least dense value by any chosen pattern
Business rule application (possibly a separate article);
1. Rule controlled matching
2. Rule controlled de-duplication

I think the simple cases are covered.

It would be good to see an article that deletes the first or last value by some sequence. A separate piece could deal with having a pattern.

I hadn't thought about density, but there's an article in there as well.