duplicate column data

In a table I have users registering for a mailing. The mailing should be
limited to one per household, so I would like to exclude all but 1 from each
unique home address. There is a unique id for each registrant, but its
possible another family member could have registered more than once with the
same home address. Whats the best way to do this?
TIA
VanIt depends on how the family members are represented in your schema. Is
there some kind of relationship which identifies one registrant as a family
member of another? Is so, to which registrant should the mailing be
addressed?
Please post your table schema, a few sample data & expected results so that
others can better understand your requirements.
Based on similar requests posted in this forum, the query most likely look
something along the lines of:
SELECT *
FROM tbl t1
WHERE < registrant Identifier > =
( SELECT MAX( < registrant Identifier > )
FROM tbl t2
WHERE t2. < address Identifier > = t1. < address Identifier > )
where < registrant Identifier > is the column or set of columns which
uniquely identifies a registrant while < address Identifier > is column or
set of columns which uniquely identifies an address.
Anith|||Thanks for your reply. Unfortunately I got an out of memory exception when I
tried that query.
As you suggested to post my current query looks like this:
SELECT FirstName, LastName, Email, Address1, Address2, City, State, Zip
FROM Orange_Members
INNER JOIN Orange_MembersPromo ON Orange_Members.RegId =
Orange_MembersPromo.RegId
WHERE(Orange_MembersPromo.PromoId = 600)
In the Orange_Members table there may be Address1 values that are duplicate.
There is a unique id primary key in Orange_Members called RegID.
I would like to return every unique Address1 row. In otherwords if Address1
has:
120 Greene Street twice, I would only like to have the first one that
occurs(actually I really dont care which row it is, as long as it only comes
up once) -- and of course any Address that is only in one row in the DB
should be returned by the select also.
TIA

> Based on similar requests posted in this forum, the query most likely look
> something along the lines of:
> SELECT *
> FROM tbl t1
> WHERE < registrant Identifier > =
> ( SELECT MAX( < registrant Identifier > )
> FROM tbl t2
> WHERE t2. < address Identifier > = t1. < address Identifier > )
> where < registrant Identifier > is the column or set of columns which
> uniquely identifies a registrant while < address Identifier > is column or
> set of columns which uniquely identifies an address.
> --
> Anith|||>> As you suggested to post my current query ..
Actually I asked you to post your table schema, a few sample data & expected
results. For details refer to www.aspfaq.com/5006
Based on your narrative, here is another guesswork:
SELECT *
FROM Orange_Members m1
WHERE m1.RegId = ( SELECT MAX( m2.RegId )
FROM Orange_Members m2
WHERE m2.Address1 = m1.Address1 )
AND m1.PromoId = 600 ;
Anith|||Thanks, that worked!