Finding Duplicates within the same table

[nice_true]

Hi,

I would like to know how to find duplicate records based on exact match on some columns and slight difference on others...
for example, I have a table with the following fields

1. Name nvarchar(50)
2. City nvarchar(50)
3. State nvarchar(50)
4. Age int(10)

Now, I wish to search for records that have similar values in the first three columns and differ in the last column (age) by a value less than or equal to 5.

For example

Name City State Age

ABC MUMBAI MAHARASHTRA 51
PQR KOLKATA WEST BENGAL 51
ABC MUMBAI MAHARASHTRA 51
PQR BANGALORE KARNATAKA 51
ABC MUMBAI MAHARASHTRA 49

This should return first, thrid and fifth records as 51-51=0 (which is less than 5) and 51-49=2(which is also less than 5)

Thanks....................

[pritaeas]

search for records that have similar values in the first three columns

Similar for a human eye is not that easy to translate into a "simple" query. The only way to do that is to create a lookup table containing your "similar" values. Some similarities may be programmed with a regular expression, but others are not that easy.

[nice_true]

Thanks, but any idea how to do it?

[pritaeas]

You could do an inner join on the same table, something like this, may need tweaking:

SELECT * FROM yourtable a
INNER JOIN yourtable b 
ON a.Name = b.Name
AND a.City = b.City
AND a.State = b.State
AND a.Age - b.Age BETWEEN -5 AND 5