remove duplicate records from a table

Question

Post reply

remove duplicate records from a table

jaffrey

Mr or Mrs. 500

Points: 530
More actions
October 15, 2010 at 6:15 am

#143184

hai genius,
Can anybody please help me out? My question is ,how to remove duplicate records from a table?

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please can we have table DDL and sample data in the form of INSERT statements. Then we'll be able to help you.

Thanks

John

jaffrey Mr or Mrs. 500 Points: 530 More actions · Answer 2

This is the sample table and insert statements:

create table dup(id int name varchar(50))

insert into dup values (1 'raja')

insert into dup values (2 'krishna')

insert into dup values (3 'ramana')

insert into dup values (4 'prakash')

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I take it your comma key is missing or your Parse button doesn't work?

Anyway, I corrected your code and here are your options:

(1) Create a new table and select filtered data into it, something like this:

select MAX(id), MAX(name) from dup

group by id, name

Then delete the original table and rename the new table to the same name as the original.

(2) Run this:

WITH Numbered AS (

SELECT id, name, ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id, name) AS seq

FROM dup

)

DELETE FROM Numbered

WHERE seq > 1

If your table consists of more columns than just those that make up the "primary key" (in inverted commas because your table does not appear to have one) then you will want to devise a method of choosing which rows to remove.

John

jaffrey Mr or Mrs. 500 Points: 530 More actions · Answer 4

hai john,

Thanks in advance

As i gone through your reply

I dont know how to implement the 1st of ur option in my table,You said Create a new table and select filtered data into it,If possible tell me elaborately...

When i come to 2nd one , u have mentioned "name" before and after ORDER BY, what to replace in this place of "name" ?, can u please clarify my doubt ,because i have faced this question in the interview

(2) Run this:

WITH dup AS (

SELECT id, ename, ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id, name) AS seq

FROM dup

)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Ah, so this is an interview question!

(1) Create the table and use the SELECT statement to add the data into it.

(2) "name" is the name of the column you supplied in your table "DDL".

John

jaffrey Mr or Mrs. 500 Points: 530 More actions · Answer 6

sorry since i am new to sql server i dont understand, so please tell me the exact query to execute for the 1st one..I tried but it wont work ,i guess i making mistake...

Regarding the 2nd one it wont work i am executing the same query...

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 7

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

refer the above link... 😉