We recommend that you have JavaScript enabled to enjoy the awesomeness of this site!

  • The page you are on right now   ⟶
  • EM Articles   ⟶
  • Finding Duplicate Records

Finding Duplicate Records

This past weekend I had to find duplicate records in a table in one of my databases. I recalled staring at the screen thinking to myself, "Man, I know I have done this. How did I go about doing this. CRAP!" Then it clicked. I had an existing script that I did from a previous project tucked away in my SQL script bag of tricks. The script below is the most basic script used to find duplicates. The main components of this scripts are the count() function, group by clause, and the having clause.

select pc, count(pc) as pctotal
from locations_tbl
where pc != ''
group by pc
having(count(pc) > 1)

The output from the scripts above(columnnam=pc, columnname_total=pc_total):

Now that I have this info I really needed to see the details of the duplicates so I could address them. So I took the script from above, and wrote a new script that utilized that script as a derived table that I joined to the new script. See my script below:

select lt.comp_name, lt.address, lt.pc
from locations_tbl as lt
        inner join (
        select pc, count(pc) as pctotal
from locations_tbl
where pc != ''
group by pc
having(count(pc) > 1)
) as lt2
        on lt.pc = lt2.pc
where lt.visible = 1
order by lt.pc

Here is the results from the query above: