15,965,771 members
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

## 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

v2
[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

## 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```

Varun Sareen 17-Feb-12 7:22am
good work sarvesh jee

## Solution 3

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 ```

## Solution 6

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```

v2
André Kraak 18-Feb-12 12:36pm
Edited solution:

## 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```