how to delete duplicate rows from table
Answers were Sorted based on User's Feedback
Answer / kumar.t
Name : New
Sno Name
1 Rajesh
2 Rajesh
3 Raja
4 Raja
5 Arun
6 Bala
Delete From New Where Sno Not IN
(Select Min(Sno) From New Group By Name)
Is This Answer Correct ? | 14 Yes | 0 No |
Answer / anand
TABLENAME: testanand
DATA:
id vanme emailid age
-- ------ ---------- ------
1 Anand anand.kv@abc.com 29
2 Anand anand.kv@abc.com 28
3 Rajesh rajesh@abc.com 30
4 Rajesh rajesh@abc.com 31
5 Vinit vinit@abc.com 21
6 Vinit vinit@abc.com 25
DELETE FROM testanand WHERE id Not IN
(SELECT min(id) FROM testanand GROUP BY vname)
RESULTS:
id vanme emailid age
-- ------ ---------- ------
1 Anand anand.kv@abc.com 29
3 Rajesh rajesh@abc.com 30
5 Vinit vinit@abc.com 21
Is This Answer Correct ? | 8 Yes | 1 No |
Answer / divya mahendra sikarwar
Execute queries:
CREATE TABLE dbo.Test1 (
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]
INSERT INTO Test1 VALUES(1, ‘Bob’,'Smith’)
INSERT INTO Test1 VALUES(2, ‘Dave’,'Jones’)
INSERT INTO Test1 VALUES(3, ‘Karen’,'White’)
INSERT INTO Test1 VALUES(1, ‘Bob’,'Smith’)
INSERT INTO Test1 VALUES(4, ‘Bobby’,'Smita’)
select identity(int,1,1) as SlNo,* into #temp from Test1
DELETE
FROM #temp
WHERE SlNo NOT IN
(
SELECT MAX(SlNo)
FROM #temp
GROUP BY ID,FirstName,lastname
)
drop table test1
select * into test1 from #temp
alter table test1 drop column SlNo
select * from test1 order by id
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / kumar.t
Navaneethakrishnan your query only delete id 1 but not
delete remaining records.
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / divya mahendra sikarwar
SELECT FormID,
FormCode,
LangCode,
count(*)
FROM adzForm
GROUP BY FormID,
FormCode,
LangCode
HAVING COUNT(*)>1
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / monty
i have other solution if you have table employee with
duplicate rows. You can create other table that doesn't
have duplicate rows
select distinct * into newtable from employee
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / gayathri
WITH T1 AS (Select *, ROW_NUMBER() OVER (PARTITION BY id
Order By id) AS rowid From test)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / navaneethakrishnan
Hi Kumar,
Yes it will delete only the id=1, you have to change the
id=2 whichever u want...
DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 2
DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 3
For ur kind information,.. it will delete the duplicate
records only if there is exactly 2 duplication...... if the
id 1 will entered to three times, this query will delete
only one record... because
as we enterd the query as top(1)it will select the top
most and delete the record..
This is the simple query to execute.... there is other ways
also.. to do....
Now use this query to do for more than 2 duplication
DELETE TOP(SELECT COUNT(*) -1 from dbo.tblduplicate where
id = 1)
from dbo.tblduplicate
where id = 1
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / navaneethakrishnan
table name : tblduplicate
Ans : DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 1
Example :
CREATE TABLE tblduplicate(ID int NOT NULL, Name varchar(50))
insert into tblduplicate values(1, 'Name1')
insert into tblduplicate values(1, 'Name1')
insert into tblduplicate values(2, 'Name2')
insert into tblduplicate values(2, 'Name2')
insert into tblduplicate values(3, 'Name3')
insert into tblduplicate values(3, 'Name3')
select * from tblduplicate
Result :
1 Name1
1 Name1
2 Name2
2 Name2
3 Name3
3 Name3
DELETE TOP(1) FROM [tblduplicate] WHERE [ID] = 1
Is This Answer Correct ? | 1 Yes | 7 No |
Mention the different authentication modes in sql server.
Write a SQL Query to find first Week Day of month?
What is the stuff?
What are the disadvantages of using querystrings to send data from one page to another?
Explain indexes disadvantages?
Explain the properties of sub-query in sql server?
How to create function without parameter in sql server?
What is a NOLOCK?
What is the sql server 2000 version number?
What is the use of DBCC commands?
Mention the uses of stored procedures.
Do you know what is rank function?