Identifying and removing the duplicate record

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)

Identifying and removing the duplicate record

Author

Topic

MrSingh
Starting Member

5 Posts

Posted - 2011-03-07 : 10:55:34

I am seeking a quick method for identifying ad removing duplicate records in a table.

From some of the forums I have visitied I am required to create a temporary holding table transferring data between the two to eradicate the duplicate.

Could you please provide some example queries to identify and delete the duplicate record across one table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-07 : 10:59:13

You should provide a sample table with sample data please.
Then you can also post the wanted result ie. which rows should stay and why not the other rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-07 : 20:51:10

this will remove the duplicate records that you want.


delete d
from  (
          select *, row_no = row_number() over (partition by col1, col2 order by col3)
          from   yourtable
      ) d
where d.row_no <> 1

Note : col1, col2 etc are the columns that you can uniquely identify the records,
col3 is used in row_number() to determine the sequence / order of the duplicate records and hence removing those duplicate records

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources