October 15, 2010 at 6:15 am
hai genius,
Can anybody please help me out? My question is ,how to remove duplicate records from a table?
October 15, 2010 at 6:19 am
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
October 15, 2010 at 6:41 am
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 (1 'raja')
insert into dup values (2 'krishna')
insert into dup values (2 'krishna')
insert into dup values (3 'ramana')
insert into dup values (4 'prakash')
October 15, 2010 at 7:12 am
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
October 15, 2010 at 8:35 am
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
)
October 15, 2010 at 8:41 am
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
October 15, 2010 at 8:57 am
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...
October 16, 2010 at 7:04 am
http://support.microsoft.com/kb/139444
refer the above link... 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply