0

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....................

2
Contributors
3
Replies
22
Views
4 Years
Discussion Span
Last Post by pritaeas
0

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.

0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.