delete duplicate row in sql server

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

General SQL Server Forums

New to SQL Server Programming

delete duplicate row in sql server

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2011-09-01 : 03:37:16

in oracle rowid represent the unique row id

how to find row_id in sql server

or how to delete duplicate row in sql server

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-01 : 04:17:13

there isn't one in SQL Server.

if you are using SQL 2005 / 2008, you can use row_number() to return a running number.

To delete duplicate rows in SQL Server :


DELETE D
FROM   (
           SELECT *, ROW_NO = ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY col2 )
           FROM   YOURTABLE
       ) D
WHERE  D.ROW_NO <> 1

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 04:38:09

regardless of any version you can use below


DELETE D
FROM YOURTABLE D
LEFT JOIN (SELECT col1,MIN(col2) AS MinCol
           FROM YOURTABLE
           GROUP BY col1)D1
ON D1.col1 = D.col1
AND D1.MinCol = D.col2
WHERE D1.col1 IS NULL

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources