eliminating duplicates

Question

eliminating duplicates

Viewing 15 posts - 91 through 105 (of 137 total)

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

d_sysuk SSC Eights! Points: 809 More actions · Answer 1

Hi Saritha,

Think you may have made the first initial mistake that I did, (whilst was determining what the poster required - as was not the clearest)..

Background

Table: Has dup rows

Requirement: [Edited to fit requirement]

Delete Only 1 Copy of the duplicate row.

Leave other copies of the duplicate row.

check the data on input/output from the script.

IN: (Or use from earlier post the SQL Script)

Emp EmpID

FRED 1

JO 2

MAX 3

OUTPUT Required:

FRED 1

JO 2

I suspect from the script that you've given we'll get:

FRED 1

JO 2

MAX 3

--> Close, but not quite there, as the script is a looping de-dup script.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

saritha (7/2/2008)
Try this
set rowcount 1
select 1
while @@rowcount > 0
delete Emp1 where 1 < (select count(*) from Emp1 a2
where Emp1.Eno = a2.Eno
and Emp1.Ename = a2.Ename
)
set rowcount 0

saritha, you try it - here's some data...

DROP TABLE #Testing

CREATE TABLE #Testing (FirstCol varChar(5), SecondCol INT, ThirdCol Char(1))

INSERT INTO #Testing (FirstCol, SecondCol, ThirdCol)

SELECT 'ABC',24,'M' UNION ALL -- remove

SELECT 'ABC',24,'M' UNION ALL

SELECT 'DEF',24,'M' UNION ALL -- remove

SELECT 'DEF',24,'F' UNION ALL

SELECT 'GHI',26,'F' UNION ALL -- remove

SELECT 'GHI',26,'F' UNION ALL

SELECT 'LMN',27,'M' UNION ALL -- remove

SELECT 'LMN',27,'M' UNION ALL

SELECT 'PQRS',25,'F' UNION ALL -- remove

SELECT 'XYZ',24,'M' UNION ALL -- remove

SELECT 'XYZ',25,'M' -- remove

😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

d_sysuk (7/2/2008)
Hi Saritha,
Think you may have made the first initial mistake that I did, (whilst was determining what the poster required - as was not the clearest)..
Background
Table: Has dup rows
Requirement:
Delete Only 1 Copy of the duplicate row.
Leave Unique Values
Leave other copies of the duplicate row.

Assuming that

Leave Unique Values

means "leave unique rows", then this is not correct. Unique rows should be removed.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

d_sysuk SSC Eights! Points: 809 More actions · Answer 4

thank-you chris, gone back and editied my incorrect values/spec

karthik M One Orange Chip Points: 29139 More actions · Answer 5

rbarryyoung,

when will your article published ? i am eagerly expecting your article.

karthik

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Just so you know... I've had it take as long a 6 weeks from the submital date to have an article published and as little as 1 week. It all depends on what the site has on its plate. So, I'm thinking we're probably not going to see Barry's article until at least a week from now... but, it'll be worth the wait. 🙂

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

It has been submitted and is awaiting approval. As Jeff said, once it is approved it can still be another 1-6 weeks before publication, but I may be able to give you a link before then.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 8

Hi

Check out the below link

http://support.microsoft.com/kb/q139444/

http://www.sql-server-performance.com/articles/dev/dv_delete_duplicates_p1.aspx

Thanks -- VJ

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Vijaya Kadiyala (7/6/2008)

Hi

Check out the below link

http://support.microsoft.com/kb/q139444/

http://www.sql-server-performance.com/articles/dev/dv_delete_duplicates_p1.aspx

Thanks -- VJ

http://dotnetvj.blogspot.com[/quote%5D

Why?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

rbarryyoung (7/6/2008)

Vijaya Kadiyala (7/6/2008)
Hi
Check out the below link
http://support.microsoft.com/kb/q139444/
http://www.sql-server-performance.com/articles/dev/dv_delete_duplicates_p1.aspx
Thanks -- VJ
http://dotnetvj.blogspot.com[/quote%5D
Why?

I agree... WHY? The methods shown on those links do not accomplish the task requested (eventually) by the original poster. And, they're both pretty slow ways of deleting dupes. Good idea, but wrong task.

--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)

vipul.ymca Valued Member Points: 50 More actions · Answer 11

I see 2 possible options but not sure if they are implementable at your end.

1. insert a temp identity column and then you can issue a delete statement. (let me know if this is acceptable and I can write a query for you)

2. Get a distinct data in a temp table, delete all the row in original table and then insert the distinct data from the temp table

HTH

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 12

vipul.ymca (7/7/2008)
I see 2 possible options but not sure if they are implementable at your end.
1. insert a temp identity column and then you can issue a delete statement. (let me know if this is acceptable and I can write a query for you)
2. Get a distinct data in a temp table, delete all the row in original table and then insert the distinct data from the temp table
HTH

Gosh... you must have read only the first post... that pretty much violates what the op needs to do...

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Heh. It's amazing how the OP not putting their requirements in the first post, then putting them in their second post, can result in a 100+ replies. And still new vistors posting their incorrect "solutions" without even bothering to read any of the replies, even though they can see that there are already over 100 replies. I would have thought that they would at least figure that this had been answered some time ago.

It's a real study in mass psychology. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 14

You're right... it's an awesome study! Proves what I've been saying all along about some folks in the IT business... they don't look, they don't read, they don't understand... they just want to get it done. Same attitude causes huge problems at work with many of the developers...

--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)

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 15

It is interesting and the title doesn't help. It's a good thing that so many people want to help, and I can sympathize. Reading 50+ replies is time consuming.

Everyone wants to chime in with their solution as well. Maybe it's time for an article on this 😉