Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
Watch the view to see the above concept in action:
[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]
Reference : Pinal Dave (https://blog.sqlauthority.com)
450 Comments. Leave new
hi
how to find 3rd maximum salary, like that how delete duplicate values in a table
Thanks
Praveen
I have already replied to many people
Refer this post and choose the effecient method
sir,
i am very much imperssed with u r ans. but if no of columes will be more than 100 then what is the procedure
please repaly to this one to my mail id if possible
Hi,
Among Joins and Subquery, which one is better approach?
Can u pls tell me this.
Thanks
Ramesh
It depnds on the specific case you are using
Usually joins are effecient
very helpful for me to delete duplicate records
Hi,
I need to delete all the duplicate records with MAPID being the duplicated FIELD where ADDRESSCOUNT = 0 being the other defining criteria. My table looks like;
ID MAPID ADDRESSCOUNT
111 54560 4
132 54560 0
198 23429 1
240 29584 1
248 29584 0
Any help appreciated.
Using MS SQL Server 2000.
Regards,
Jim
Post the expected result so that it is easy for us to write the query
how i find recent row updated i would like to use this row in trigger after update in table
hi Mr. Dave,
This question was asked to me in an interview and I was unable to answer.
Now I got the solution.
ThankU Very Much
Hi jim(54),
this should work for you.
DELETE
FROM duptest
WHERE MAPID IN
(select MAPID
from duptest
group by mapid
having count(mapid) > 1) and AddressCount = 0
cheers,
anand.
hello ur solution is rite but all duplicate record is deleted i want to keep one record from duplicacy row in table
This logic is delete all record only not be douplicate
very nice logic…u r great
–For finding second highest salary
select max(salary) from emp where salary<max(salary)
How to recover deleted records from any perticular table
in case of no id column in table one can delete duplicate rows as below
create view abc as select *,row_number() OVER (PARTITION BY dupcol1,dupcol2,… ORDER BY dupcol1,dupcol2,…) as rnum from
table
delete from abc where rnum > 1
drop view abc
Hi All,
I would like to share one suggestion that is :
should’t We conclude each topic with one best answer (if we can).
Hi,
Thank you so much for sharing your knowledge.
Great Work.
hi,
realy this is super i have not found any where very nice.
cheers,
Satish
Hello Sir,
I recently joined your site, and found it really very helpful.
How about using ‘ROWID’ to delete the duplicate rows.
Please check this query.
DELETE
FROM MyTable
WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM MyTable
GROUP BY DUPL_COL1,DUPL_COL2,DUPL_COL3)…
–All col names
Now, my question is that if I have more than two duplicate records I want to keep 2 of them and to remove rest.
How can I do it?
Please help out.
Thank you.
Hi
its a great thing to share knowledge
thanks for your help
ssatish kumar
Hi ATIN(59),
For finding only second highest salary – –
select * from
(select * from employee orderby salary desc
where rownum>=2)
minus
select * from
(select * from employee orderby salary desc
where rownum>=1) ;
And to get Only Top 2 Highest salary —
select * from
(select * from employee orderby salary desc
where rownum>=2);
Thanks & Regards
Angadi Doddappa
Thanks Pinal Dave….
U are doing a great Job…
All the Best all Of u…
Jai Hind…
Hi,
This page looks really cool, hope I will get answer for my question, I have a table with 35 columns and have duplicate rows based on 6 columns. So how do I remove duplicates and keep the original rows in the table, keep in mind table has around 500,000 rows.
You need to use thos six columns in the GROUP BY clause