Delete all row accept 1 from selected total rows in sql

Priya Bhosle

11y1.2k

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

saravanagopi s11y ago
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 Bhosle11y ago
gopi thanks for try.but the above query is not working for my question.I tried it.
saravanagopi s11y ago
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 Sabadra11y ago
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/