Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

removing duplicates Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 10:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491426
Posted Wednesday, September 4, 2013 11:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:07 PM
Points: 2,766, Visits: 7,241
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
Post #1491454
Posted Wednesday, September 4, 2013 2:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491533
Posted Saturday, September 7, 2013 7:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 8,739, Visits: 9,286
Does the removing the duplicates job include adding a constraint to make sure the problem doesn't recur?

Tom
Post #1492525
Posted Monday, September 9, 2013 11:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
Worth mentioning, but not necessary. That's not always a possibility.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492876
Posted Monday, September 9, 2013 5:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492954
Posted Tuesday, September 10, 2013 8:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:28 AM
Points: 536, Visits: 2,099
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.
Post #1493224
Posted Friday, January 31, 2014 9:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
Bump

Looking for a writeup here as an article.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1536848
Posted Sunday, February 16, 2014 3:20 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 AM
Points: 1,767, Visits: 4,623
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
Post #1541936
Posted Tuesday, February 18, 2014 8:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
These would be good. We'd be looking for 2-3 per article to keep this relatively short.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1542615
Posted Thursday, April 10, 2014 12:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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/



Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1560584
Posted Friday, April 11, 2014 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse