Compare and filter data in sql server table
Hi,
I have this table in sql server with the name address looking like this:
firstname, string
lastname, string
address, string
zip, string,
city, string
The table consists of data from different databases and I know there are duplicate addresses in there.
I would like to compare city, zip and lastname, so that those that have more than one instance in the table are left out (they are only shown one time).
How can I achieve this?
Thanks for help!
Peter
I have this table in sql server with the name address looking like this:
firstname, string
lastname, string
address, string
zip, string,
city, string
The table consists of data from different databases and I know there are duplicate addresses in there.
I would like to compare city, zip and lastname, so that those that have more than one instance in the table are left out (they are only shown one time).
How can I achieve this?
Thanks for help!
Peter
Ganapathi
Are you trying to filter them in your Select query or while inserting the table itself you want to ignore the duplicates?
do you have a unique identifier for every row? (in addition to the columns already listed)
-- e.g. if one did this to locate the "duplicates"
select city, zip, lastname, count(*)
from YourTable
group by city, zip, lastname
having count(*) > 1
i.e. is there some existing field to help identify which of the duplicated records to retain?
-- e.g. if one did this to locate the "duplicates"
select city, zip, lastname, count(*)
from YourTable
group by city, zip, lastname
having count(*) > 1
i.e. is there some existing field to help identify which of the duplicated records to retain?
ASKER
I'm going to filter in the select query.
Peter
Peter
ASKER
the unique identifier is customerID, int. I'm sorry I omitted that.
Peter
Peter
>>I'm going to filter in the select query
is this something like what you are looking for?
select city, zip, lastname, min(customerID) as customerID
from YourTable
group by city, zip, lastname
by the way, what happens if a family (same city, same zip, different homes) each become a customer?
is this something like what you are looking for?
select city, zip, lastname, min(customerID) as customerID
from YourTable
group by city, zip, lastname
by the way, what happens if a family (same city, same zip, different homes) each become a customer?
ASKER
Thanks for answer. It's almost what I want.
Say that I would like to add firstname and address in my select statement, but I don't want to group by them since then the filter will also apply to those columns. How can I do that?
Peter
Say that I would like to add firstname and address in my select statement, but I don't want to group by them since then the filter will also apply to those columns. How can I do that?
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.