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 12»»

removing duplicates Expand / Collapse
Author
Message
Posted Wednesday, September 04, 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 @ 4:56 PM
Points: 32,771, Visits: 14,935
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 04, 2013 11:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:10 AM
Points: 2,694, Visits: 6,895
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 04, 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 @ 4:56 PM
Points: 32,771, Visits: 14,935
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 07, 2013 7:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
Does the removing the duplicates job include adding a constraint to make sure the problem doesn't recur?

Tom
Post #1492525
Posted Monday, September 09, 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 @ 4:56 PM
Points: 32,771, Visits: 14,935
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 09, 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 @ 10:03 PM
Points: 35,956, Visits: 30,244
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." -- 04 August 2013
(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: Yesterday @ 4:00 AM
Points: 522, Visits: 2,013
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 @ 4:56 PM
Points: 32,771, Visits: 14,935
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 228, Visits: 1,255
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 @ 4:56 PM
Points: 32,771, Visits: 14,935
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse