Trying to find duplication in records where address is different only in the one field and only by a certain number

Question

I have a table of listings that has NAP fields and I wanted to find duplication within it - specifically where everything is the same except the house number (within 2 or 3 digits).

My table looks something like this:

Name Housenumber Streetname   Streettype City       State  Zip
1    36          Smith        St         Norwalk    CT     6851
2    38          Smith        St         Norwalk    CT     6851
3     1          Kennedy      Ave        Campbell   CA     95008
4     4          Kennedy      Ave        Campbell   CA     95008

I was wondering how to set up a qry to find records like these.

I've tried a few things but can't figure out how to do it - any help would be appreciated.

Thanks

Answer 1

Are you looking to find something that shows the amount of these rows you have like this?

SELECT 
    StreenName,
    City,
    State,
    Zip,
    COUNT(*)
FROM YourTable
group by StreenName, City, State, Zip
HAVING COUNT(*) >1

Or maybe trying to find all of the rows that have the same street, city, state, and zip?

SELECT 
    A.HouseNumber,
    A.StreetName,
    A.City,
    A.State,
    A.Zip
FROM YourTable as A
INNER JOIN YourTable as B
    ON A.StreetName = B.StreetName
    AND A.City = B.City
    AND A.State = B.State
    AND A.Zip = B.Zip
    AND A.HouseNumber <> B.HouseNumber

Answer 2

Here is one way to do it. You'll need a unique ID for the table to run this, as you wouldn't want to select the exact same person if theyre the only one there. This'll just spit out all the results where there is at least one duplicate.

Edit: Woops, just realized in comments it says varchar for the street number...hmm. So you could just run a cast on it. The OP never said anything about house numbers in varchar or being letters and numbers in the original post. As for letters in the street number field, I've been a third party shipping provider for 2 yrs in the past and I have never seen one; with the exception of an apt., which would be a diff field. Its just as likely that someone put varchar there for some other reason(leading 0's), or for no reason. Of oourse there could be, but no way of knowing whats in the field without response from OP. To run cast to int its the same except this for each instance: Cast(mt.HouseNumber as int)

 select *
 from MyTable mt
 where exists (select 1
               from MyTable mt2
               where mt.name = mt2.name
               and mt.street = mt2.street
               and mt.state = mt2.state
               and mt.city = mt2.city
               and mt2.HouseNumber between (mt.HouseNumber -3) and (mt.HouseNumber +3)
               and mt.UID != mt2.UID
               )
 order by mt.state, mt.city, mt.street
 ;

Not sure how to run the -3 +3 if there are letters involed...unless you know excatly where they are and you can just simply cut them out then cast.