Home > Oracle, SQL Server > SQL Query Trick to find duplicate records

SQL Query Trick to find duplicate records

Over the past few years there have been numerous instances where I needed to find records in a table where one field was different but two others were the same.  This proved painful until a friend of mine turned me on to this trick below.  You can use an inner join on the table itself to find the records. 

The syntax is simple yet it works:

select * from table1 a inner join table1 b where a.id <> b.id and a.Column = b.Column;

For an easy test I am going to select records from the Sample Northwind Database Products table.  There are records that have the same SupplierID and CategoryID field but different ProductID’s (primary key). 

select
  a.productid,
  a.productname,
  a.SupplierID,
  a.CategoryID,
  b.productid,
  b.productname,
  b.SupplierID,
  b.CategoryID 
  from Products a
  inner join Products b on a.ProductID <> b.ProductID
  where a.SupplierID = b.SupplierID
  and a.CategoryID = b.CategoryID

 

And below you see the records desired (duplicate column data). 

1-8-2010 12-37-17 AM

About these ads
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.
Follow

Get every new post delivered to your Inbox.

Join 166 other followers

%d bloggers like this: