Delete all row accept 1 from selected total rows in sql

Question

I have one table it contains many duplicate rows for single value.for eg below suppose there is one record with above ACCS_NO= 004068 value.with this value there are total 3 rows with same records accept doc_no.so I want to delete top 2 from 3 records i.e. doc_no=008446 & 008447(here in this table doc_no is auto generated id).I want to do this for whole table at one time in sql only. please help to solve this problem.how can I do this?I want to do this for all duplicate records?

saravanagopi s · Answer

select row_number()over (partition by ACCS_No order by Doc_No desc) as Row* from question what is the o/p this above query returns? there should be new column as Row Thats Ranking based on Accs_No try and send screen shot of table

Priya Bhosle · Answer

gopi thanks for try.but the above query is not working for my question.I tried it.

saravanagopi s · Answer

Delete A from (select row_number()over (partition by ACCS_No order by Doc_No desc) as Row* from question)A where A.row > 1 pls check that query using below query and check your result and then delete using above query select * from (select row_number()over (partition by ACCS_No order by Doc_No desc) as Row* from question)A where A.row > 1

Prasham Sabadra · Answer

Hi Please have a look at following links once http://support.microsoft.com/en-us/kb/139444 http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/