How to delete duplicate data?` [Resolved]

Posted by Mandlaa under Sql Server on 6/10/2014 | Points: 10 | Views : 4019 | Status : [Member] | Replies : 4
I have a table &data like below

Id FName LName
1 aaa aaa
1 ttt ttt
1 fff fff
.
.
.


I want to delete all duplicate records,
In the above table delete all records except one record

I want this type of output
Id FName LName
1 aaa aaa

Give me the query




Responses

Posted by: Kirthiga on: 6/11/2014 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Mandlaa,

Try this below query for your required output

--Sample table

create table test (Id int, FName varchar(5), LName varchar(5))
insert into test values (1,'aaa','aaa'),(1,'ttt','ttt'),(1,'fff','fff')

select * from test

--Delete Query
;with del as(
select Id,ROW_NUMBER()over(order by Id)rn from test
)delete from del where rn>1

select * from test




Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 6/10/2014 [Member] [MVP] Silver | Points: 25

Up
0
Down
http://stackoverflow.com/questions/6103212/how-do-i-delete-duplicate-rows-and-keep-the-first-row


If this post helps you mark it as answer
Thanks

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 6/11/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
create table test (Id int, FName varchar(5), LName varchar(5))

insert into test values (1,'aaa','aaa'),(1,'ttt','ttt'),(1,'fff','fff')

--To display only one record from duplicate data
SELECT ID, FName, LName FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id ) RN FROM Test) Temp
WHERE Temp.RN=1

-- To Delete duplicate data from original table
DELETE Temp
FROM (SELECT ID, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Id ) RN FROM Test) Temp
WHERE Temp.RN>1

select * from test


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Adsingh_Naz on: 6/20/2014 [Member] Starter | Points: 25

Up
0
Down
best approach to remove or delete duplicate rows in sql server is published in the below url

http://dotnetbites.com/delete-duplicate-rows-sql-server

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response