duplicate rows delete - 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

General SQL Server Forums

New to SQL Server Programming

duplicate rows delete

Author

Topic

bheema
Starting Member

12 Posts

Posted - 2011-11-16 : 01:07:12

hi,
this is bheema i want delete all duplicate rows at a time like this

sno name sal

1 bheema 5000
1 bheema 5000
1 bheema 5000
1 bheema 5000

in this i want delete 3 duplicate rows at a time.i need o/p like this

o/p: sno name sal

1 bheema 5000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 01:14:52

[code]
delete t
from (select row_number over (partition by sno,name,sal order by newid()) as rn
from table
)t
where rn >1
[/code]

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

bheema
Starting Member

12 Posts

Posted - 2011-11-16 : 01:59:39

any simple query is thr for this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 02:04:54

quote:
Originally posted by bheema

any simple query is thr for this?

what does that mean?
why above is not simple?

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

bheema
Starting Member

12 Posts

Posted - 2011-11-16 : 02:09:02

in this error is coming error is ora-00933 sqlcommand not properly ended

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 03:33:20

quote:
Originally posted by bheema

in this error is coming error is ora-00933 sqlcommand not properly ended

Ok so you're using oracle and in wrong forum

this is ms sql server forum so solutions given here are guaranteed to work only in sql server.
Try this also and if its also not working please try your luck in oracle forums like www.orafaq.com


delete t
from (select row_number over (partition by sno,name,sal order by sno) as rn
from table
)t
where rn >1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources