Greetings,I have the following duplicate address checker: SELECT firstname, lastname, address1, City, state FROM ccncontacts GROUP BY state, city, address1, firstname, lastname HAVING COUNT(*) > 1
what I'd like to do is modify this query to return rows that have matching first 5 characters of the address1 column as well. The reason I want to do this is so that I can pick out 2+ rows that are nearly identical, but for instance one has an address1 of "21 Yancy St." and another row has "21 Yancy Street"Conceptually, what I'd like to do is this:SELECT firstname, lastname, LEFT(address1, 5) as adrs1, City, state FROM ccncontacts GROUP BY state, city, adrs1, firstname, lastname HAVING COUNT(*) > 1
but when I try that, the adrs1 in the GROUP BY clause causes an error ("invalid column name"). Any ideas on how I can improve the original query to have this functionality?**NOTE: I realize that this new "LEFT 5" query could technically create false positives (ex. 2 guys both named "Ben Grimm", and living at addresses "22 Yan Road" & "22 Yancy Street" respectively). I'm OK with that. I'd manually check each result returned, and verify it is indeed a duplicate before deleting one of the rows manually. I'm running this query once to help me tidy up a database**
[solved] Making a duplicate address checker better
Posted - 2011-09-21 : 15:26:13
