deleting duplicate rows in database

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 2005 Forums

Transact-SQL (2005)

deleting duplicate rows in database

Author

Topic

sbglobal
Starting Member

4 Posts

Posted - 2010-08-11 : 07:51:53

Dear visitor !

we are using sql server 2005 . we want to delete duplicate raws from table record . Please guide us how to do this one.

Thanks in advance !

www.sbglobal.info

PavanKK
Starting Member

32 Posts

Posted - 2010-08-11 : 08:00:30

try this. This will keep one record & will delete remaining duplicate

------

DECLARE @Tbl TABLE (Year INT,Grade VARCHAR(10))

INSERT INTO @Tbl
select 2007,'Grade 1' union all
select 2007,'Grade 1'union all
select 2007 ,'Grade 1' union all
select 2008 ,'Grade 2' union all
select 2008 ,'Grade 2' union all
select 2008 ,'Grade 3' union all
select 2009 ,'Grade 1'union all
select 2009 ,'Grade 2'union all
select 2009 ,'Grade 4' union all
select 2009 ,'Grade 5'

SELECT * FROM @Tbl ORDER BY Year

;WITH cte AS
(
SELECT YEAR,Grade,ROW_NUMBER()OVER(PARTITION BY year,grade ORDER BY (SELECT NULL)) AS Rno
FROM @Tbl
)

DELETE FROM cte
WHERE Rno>1

SELECT * FROM @Tbl ORDER BY Year

----------

KK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 10:31:37

no need of CTE. you can just use a derived table instead


DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY year,grade ORDER BY (SELECT NULL)) AS Rno
FROM Table
)t
WHERE Rno >1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources