eliminating duplicates

Question

eliminating duplicates

vish-531091

Right there with Babe

Points: 738
More actions
June 19, 2008 at 4:45 am

#183590

Hello All,
i have a table with dupicate records.
I wish to delete the first row among the duplicate records and keep the others :hehe: and the other way round.
How should have right the DELETE statement with top 1 clause in it. :crazy:
Thanks in Advance..!!

Viewing 15 posts - 1 through 15 (of 137 total)

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

shailesh-519005 SSChasing Mays Points: 643 More actions · Answer 1

Hi vish

u have not mentioned which version of sql server u r using because if u are using sql server 2000 then u will not be able to use Delete top clause.

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

Simply you can use DISTINCT keyword.

If you give some sample data's you will get lot of help from here.

karthik

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

Say for example,

create table #Emp

(

Eno int,

Ename varchar(5)

)

insert into #Emp

select 1,'AA'

union all

select 1,'AA'

union all

select 2,'BB'

union all

select 3,'CC'

select distinct * into #FinalResult

from #Emp

will give you the expected result.

karthik

John Rowan SSC Guru Points: 56440 More actions · Answer 4

How you do this depends on a couple of factors. How many rows are in your table? How many rows are dupes that need to be deleted?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

vish-531091 Right there with Babe Points: 738 More actions · Answer 5

Hi All,

Let me give my problem description in detail.

I use SQL server 2000.

I have a table 'TAB' with 3 coulumns 'Name','Age','Sex'

and there is no primary key.

I have multiple duplicate data in my table. for eg..

ABC24M

LMN27M

PQRS25F

XYZ24M

XYZ25M

Now i would wish to 'DELETE' the 1st original row ( Row no 1,3,6,7)

and keep the other duplcates from above data. 😎

Now, If there is a row without any dupicates present.. it will be deleted ( for eg Row no 6)

The condition is i dont want to go for intermediate tables or have any

additional identity column. :w00t:

Please help..!!

Thanks folks

Sergiy SSC Guru Points: 110208 More actions · Answer 6

vish,

I don't see any numbers assigned to the rows.

How can I tell which ones are 1,3,6,7?

_____________
Code for TallyGenerator

vish-531091 Right there with Babe Points: 738 More actions · Answer 7

Hi,

There no row numbers associated.. i just wanted to make it clear..

through that example.

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 8

vish (6/19/2008)i just wanted to make it clear..

You did not succeed.

For me these lines:

LMN 27 M

look absolutely identical.

If it's all you've go in table they are identical for SQL Server as well.

Open this table in EM and try to remove one of the rows.

I will not do because of "not enough key information".

If you try to delete it from QA you need to specify which row(s) to delete:

DELETE

FROM Table

WHERE ... = 'LMN' AND ... = 27 AND ''' = 'M'

AND ?????

Without specifying what do you mean by ????? all 3 rows will be deleted.

So, you need to define the criteria for the deletion more specifically.

_____________
Code for TallyGenerator

vish-531091 Right there with Babe Points: 738 More actions · Answer 9

hi sergiy,

Thats right..

Those rows are practically identical...

So the point is we need to have some condition which will distinguish them from each other...

So i feel its quite impossible under such given conditions.

I think i get the blackhole.

Thanks to all for your responses..!!

vish 😎

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 10

Hi,

U can use RowID property to delete the duplicate records, I think.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Can you explain what the point to have 3 identiacl rows?

And especially what's the point of keeping 2 of 3 identiacal rows?

Does it make any difference which 1 of 3 rows to delete if the all are IDENTICAL?

_____________
Code for TallyGenerator

goodguy SSCertifiable Points: 5456 More actions · Answer 12

Although you have stated you do not wish to go through intermediate tables, the most suitable solution for you would be to select distinct rows from TAB into a TEMP table, delete all from TAB, and insert the records from TEMP back into TAB. Then make sure you have placed enough checks and constraints to ensure no duplicates creep in again. A simple UNIQUE index on a combination of key columns will suffice.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Satyendra Gupta SSC Enthusiast Points: 149 More actions · Answer 13

alter table add ids int identity

delete MN

from MN

Inner Join (select ) AL

ON AL. AL.ids

alter table drop column ids

John Rowan SSC Guru Points: 56440 More actions · Answer 14

The condition is i dont want to go for intermediate tables or have any
additional identity column

Who has placed these conditions on your solution? The only way I can see you having to even attempt this with those conditions in place is if this is a homework assignmnet.

There are some quite simple methods to do what you are asking. Do you still want help with a solution?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden