Removing duplicates from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Removing duplicates from a table

Author

Topic

Gigabyte
Starting Member

30 Posts

Posted - 2012-06-14 : 16:18:21

create table ForDuplicates (id int, name varchar(20))
insert into ForDuplicates values (1,'RAM'),(2,'LAXMAN'),(3,'YUDHI'),(1,'RAMA')
select * from ForDuplicates

Now I want to delete duplicates from the above tables.

Any suggestions??

Thanks in advance ! !

GIGABYTE+

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-14 : 16:24:30

[code]
declare @ForDuplicates table (id int, name varchar(20))
insert into @ForDuplicates values (1,'RAM'),(2,'LAXMAN'),(3,'YUDHI'),(1,'RAMA')

select *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id DESC) Rn
from @ForDuplicates

;with cteGig(id, name, rn)
AS
(
select *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id DESC) Rn
from @ForDuplicates
)
select * from cteGig where rn = 1
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-14 : 16:29:21

Assuming the ID represents the duplicate value, here is a DELETE statement based on Yasiasz code:

DELETE A
FROM
(
	select *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id DESC)  Rn
	 from @ForDuplicates
 ) AS A
where rn <> 1

Gigabyte
Starting Member

30 Posts

Posted - 2012-06-14 : 19:44:09

Thanks guys ...

It worked ....

GIGABYTE+

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources