duplicate record - 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 record

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-03 : 01:46:49

hi

i am having table
ie
student_id student_nm student_date
1 john 12-09-89
2 rina 23-08-78
0 ---- 00-00-00
0 ---- 00-00-00

so how i can delete this duplicate record in sql server 2008

anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 01:48:31

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

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

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-03 : 02:21:10

ya thanks for ur reply
but i got error
"Incorrect syntax near the keyword 'over'."

anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 02:22:59

missed ()


delete t
from (select row_number() over (partition by student_id,student_date order by newid()) as rn
from table
)t
where rn >1

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

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-03 : 02:27:41

ya thanks

anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 02:32:13

wc

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-03 : 09:32:12

Hi,

Please check below link. Same thing is explained very nicely. Worth reading.

http://support.microsoft.com/kb/139444

How to remove duplicate rows from a table in SQL Server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources