Delete duplicate rows - SQL Server Forums

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)

Delete duplicate rows

Author

Topic

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-04-03 : 00:46:16

Hi All,

I had one table with duplicate rows.
How to delete duplicate rows with out using top function.

thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-03 : 01:03:42

Can you provide sample data and Expected Output.

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-04-03 : 01:15:49

Actually i had the data in my table as :
col1 col2
-------------
2 XX1
1 XX
2 XX1
2 XX1
1 XX
3 XX3

i want to delete or select duplicate records with out using TOP function is it possible?

thanks

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-03 : 01:28:44

Yup.Using Row_Number Function
please Execute t his and see



select * from 
(select ROW_NUMBER()over(partition by col1,col2 order by col1 desc,col2 desc) as seq,id
col1,col2 from @a
)t where seq>1

Delete


DELETE t FROM
(select ROW_NUMBER()over(partition by col1,col2 order by col1 desc,col2 desc) as seq,
col1,col2 from @a
)t where seq>1

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-04-03 : 01:34:01

Hi,
i am getting error as :

for this query:
select * from
(select ROW_NUMBER()over(partition by COL1,COL2 order by COL1 desc,COL2 desc) as seq,id
COL1,COL2 from XX
)t where seq>1

Msg 207, Level 16, State 1, Line 2
Invalid column name 'id'.

what to do please...

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-03 : 01:35:00

TRY THIS
select * from
(select ROW_NUMBER()over(partition by COL1,COL2 order by COL1 desc,COL2 desc) as seq
COL1,COL2 from XX
)t where seq>1

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-04-03 : 01:40:07

To Know more about Multi Purpose Row_Number Function,Request you to see this url,HTH

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

asuni
Yak Posting Veteran

55 Posts

Posted - 2010-04-03 : 01:42:53

ok, thank you very much, i will check

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources