Delete Duplicate Rows in Table

Question

4.00/5 (1 vote)

See more:

I have 1 table Name is NameInfo and inside NameInfo i have 1 column.
the column name is Name like following

Table :- NameInfo
Column :- Name

I have Following Data

Name:
James
James
Mike
David
James
Mike
David

Now i want to delete Duplicate Rows using only 1 query
so, output will be

Name:
James
Mike
David

Posted 17-Feb-12 0:07am

Member 8499806

Add a Solution

7 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Varun Sareen · Answer 1 · 2012-02-17T00:20:00

Solution 2

Dear Friend,

Following code is useful to delete duplicate records.

WITH CTE (COl1, DuplicateCount)
AS
(
SELECT COl1,
ROW_NUMBER() OVER(PARTITION BY COl1 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

Thanks

Posted 17-Feb-12 0:20am

Varun Sareen

Updated 18-Feb-12 6:06am

v2

Comments

[no name] 18-Feb-12 7:10am

but ID is not avail in given table there is only name column.

Varun Sareen 18-Feb-12 12:02pm

ohh!! yea...now i have updated my answer

Aniket Yadav · Answer 2 · 2012-02-17T00:18:00

Solution 1

follow this link

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/[^]

Posted 17-Feb-12 0:18am

Aniket Yadav

Sarvesh Kumar Gupta · Answer 3 · 2012-02-17T01:07:00

Solution 4

Hi, use below query

SQL

Select *, ROW_NUMBER() over (order by (select 0)) as RowNum into #tran1 from table1
Select * from #tran1
Delete from #tran1 where RowNum not in (Select MIN(RowNum) from #tran1 group by UName)
Select * from #tran1

Posted 17-Feb-12 1:07am

Sarvesh Kumar Gupta

Comments

Varun Sareen 17-Feb-12 7:22am

good work sarvesh jee

MaulikDusara · Answer 4 · 2012-02-17T00:23:00

Your Solution Here:

SQL

select * into #Temp FROM(
select [name],count([name]) AS [Name] from person
group by first_name having count(first_name)>1)tblTemp


select * from #Temp

delete from person where [name] in (select [Name] from #Temp)

insert into Person select * from #temp

drop table #temp

Shivapragasam · Answer 5 · 2012-02-17T01:32:00

Use the following query to delete the duplicate record in the Table.

SQL

SET NOCOUNT ON
SET ROWCOUNT 1
WHILE 1 = 1
   BEGIN
      DELETE   FROM table-name
      WHERE  col-name IN (SELECT  col-name
                               FROM    table-name
                               GROUP BY col-name
                               HAVING  COUNT(*) > 1)
      IF @@Rowcount = 0
         BREAK ;
   END
SET ROWCOUNT 0

Costica U · Answer 6 · 2012-02-18T06:50:00

Solution 7

see:
How to remove duplicate rows in SQL Server 2005 when no key is present[^]
How to remove duplicate rows in SQL Server 2008 when no key is present[^]

Posted 18-Feb-12 6:50am

Costica U

sachin10d · Answer 7 · 2012-02-18T07:23:00

Solution 8

Try this

SQL

;with cte as( 
select Name,  
row_number() over (partition by Name order by Name) rn 
from NameInfo
) 
delete cte where rn > 1

Posted 18-Feb-12 7:23am

sachin10d