I am trying to find a way to eliminate duplicate addresses from a table. I
have a table that is imported from a seperate system so I have no control
over it but I need to be able to design a query that brings out last name,
first name, zip code, city, and state, address1 and address2 for a mailing
list.
Currently I have many people at the same address that I need to remove and
when I check the zip code against our zipcode table it removes any with a zi
p
+4 setup.
Is there any way to set this up to keep the zip+4 part and eliminate all of
the duplicate addresses?
You can modify the "double cursor" example at:
http://groups.google.com/group/micr...cbce3491367da2f
Where I have
declare curOrders cursor FAST_FORWARD for select OrderID , OrderDate
from Orders where CustomerID = @.CustomerID
put a counter there. (of course reset it for each new Customer)
if the counter > 1 (aka, #2, #3, etc , etc) you can run a DELETE.
..
Its a hack, but it works.
"Don" <djw@.nospam.postalias> wrote in message
news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all
of
> the duplicate addresses?|||I am sorry, I did not state my problem completely. I also cannot delete fro
m
the original table so it must all be done in a query
"sloan" wrote:
>
> You can modify the "double cursor" example at:
> http://groups.google.com/group/micr...cbce3491367da2f
> Where I have
> declare curOrders cursor FAST_FORWARD for select OrderID , OrderDate
> from Orders where CustomerID = @.CustomerID
> put a counter there. (of course reset it for each new Customer)
> if the counter > 1 (aka, #2, #3, etc , etc) you can run a DELETE.
> ...
> Its a hack, but it works.
>
>
>
>
> "Don" <djw@.nospam.postalias> wrote in message
> news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I
> zip
> of
>
>|||I also cannot affect the original table as we rerun other queries that use
the extra information for other purposes.
I am pretty weak when it comes to SQL also, sorry.
"Don" wrote:
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all o
f
> the duplicate addresses?|||Select 'distinct' (without the quotes of course). This will eliminate
duplicates as long as you have selected all the data properly. Be careful
that you are getting all the data you want though.
"Don" wrote:
> I also cannot affect the original table as we rerun other queries that use
> the extra information for other purposes.
> I am pretty weak when it comes to SQL also, sorry.
> "Don" wrote:
>|||Why not just:
select distinct
firstname, zipcode, city, and state, address1, address2
from sometable
or you want to keep only the row with the longest zipcode? If so, try:
select
firstname, max(zipcode), city, and state, address1, address2
from sometable
group by firstname, city, and state, address1, address2
"Don" <djw@.nospam.postalias> wrote in message
news:D9647F2A-8402-40AB-A464-E8CCDEE5AD34@.microsoft.com...
> I am trying to find a way to eliminate duplicate addresses from a table.
I
> have a table that is imported from a seperate system so I have no control
> over it but I need to be able to design a query that brings out last name,
> first name, zip code, city, and state, address1 and address2 for a mailing
> list.
> Currently I have many people at the same address that I need to remove and
> when I check the zip code against our zipcode table it removes any with a
zip
> +4 setup.
> Is there any way to set this up to keep the zip+4 part and eliminate all
of
> the duplicate addresses?|||I have used distinct to get rid of duplicate entries, but distinct does not
seem to help with different people at the same address.
This is where my problem is.
First, Last, Addr1, Addr2, City, State, Zip
Joe Brown PO Box 33 NULL Town ST 99999
George Brown PO Box 33 NULL TOWN ST 99999
Jay Sherman 123 Somestrret NULL TOWN ST 99999
More specifically, I want to remove the PO Box 33 or only send to one of
them not both.
"MattB" wrote:
> Select 'distinct' (without the quotes of course). This will eliminate
> duplicates as long as you have selected all the data properly. Be careful
> that you are getting all the data you want though.
> "Don" wrote:
>|||Don what is the primary key for this table?
Say you have the customerID (ideally you should be having one), So your
query should go like this (untested, of course).
SELECT LAST_NAME, FIRST_NAME, ZIP_CODE,
CITY, STATE, ADDRESS1,ADDRESS2
FROM YOUR_TABLE A
where
CustomerID in (SELECT MIN(Customer_id) from your_table group by ZIP_CODE,
CITY, STATE, ADDRESS1,ADDRESS2 )
Let me know if this is what u r looking for.|||You could use an aggregate function (such as MIN or MAX) when you select the
names. Don't change anything related to the address information.
Keith Kratochvil
"Don" <djw@.nospam.postalias> wrote in message
news:A558C92F-EE0E-4835-9C31-BAF798905A62@.microsoft.com...
>I have used distinct to get rid of duplicate entries, but distinct does not
> seem to help with different people at the same address.
> This is where my problem is.
> First, Last, Addr1, Addr2, City, State, Zip
> Joe Brown PO Box 33 NULL Town ST 99999
> George Brown PO Box 33 NULL TOWN ST 99999
> Jay Sherman 123 Somestrret NULL TOWN ST 99999
> More specifically, I want to remove the PO Box 33 or only send to one of
> them not both.
> "MattB" wrote:
>|||Omni's subquery should work, but if you want different parameters another wa
y
is;
begin
create table ##address (LAST_NAME varchar(50) null,
FIRST_NAME varchar(50) null,
ZIP_CODE varchar(10) null,
CITY varchar(50) null,
STATE varchar(50) null,
ADDRESS1 varchar(50) null,
ADDRESS2 varchar(50) null)
set nocount on
insert ##address
SELECT distinct
null,
null,
ZIP_CODE,
CITY,
STATE,
ADDRESS1,
ADDRESS2
FROM table (NOLOCK)
WHERE your_parameter
AND no po boxes, etc
--you could also use a case stmt to eliminate wanted or unwanted parameters
--this will load the addresses only into temp table.
update ##address
set LAST_NAME = a.LAST_NAME,
FIRST_NAME = a.FIRST_NAME
FROM table a (NOLOCK)
WHERE your parameter to tie to address
select LAST_NAME,FIRST_NAME,ZIP_CODE,CITY,STATE
,ADDRESS1,ADDRESS2
from ##address (nolock)
drop table ##address
end
"Don" wrote:
> I have used distinct to get rid of duplicate entries, but distinct does no
t
> seem to help with different people at the same address.
> This is where my problem is.
> First, Last, Addr1, Addr2, City, State, Zip
> Joe Brown PO Box 33 NULL Town ST 99999
> George Brown PO Box 33 NULL TOWN ST 99999
> Jay Sherman 123 Somestrret NULL TOWN ST 99999
> More specifically, I want to remove the PO Box 33 or only send to one of
> them not both.
> "MattB" wrote:
>
