Re: SQL - using 'distinct'

Hi Oliver,

    I think you fell into the same trap I did. Your solution will return all of the unique entries, but won't return any rows for entries that have dups. I think he should do a union of your two queries (but should the first query be modified by including a distinct clause?)

regards

Jerry

oliver.willandsen_at_sg.cec.be wrote in message <6so4k5$ph0$1_at_nnrp1.dejanews.com>...
>In article <35edb134.31040591_at_news.u-net.com>,
> russ_at_u-net.net (Russell Fray) wrote:
>>
>> I have a table named 'customer'.
>>
>> I want to produce a mailing list from this table, and weed out
>> duplicates. I was trying to use :
>>
>> select distinct name, address, customer_id from customer;
>>
>> In theory this should work, but many people have more than one
>> customer_id (as they have more than one account). I want to distinct
>> on everything APART from customer_id, so if addressing information is
>> duplication only one instance is listed.
>>
>> The above example of course doesn't weed out the duplicates because
>> although name & address are the same, the customer_id is different in
>> each instance.
>>
>> I'm stuck here - any suggestions?
>>
>> Please cc replies to russ_at_u-net.net
>>
>> Regards,
>> Russell.
>>
>>
>Hi Russell,
>
>select name, address, count(*) from customer group by name, address having
>count(*) > 1
>
>gives you all the duplicates
>
>so
>
>select name, address, count(*) from customer group by name, address having
>count(*) = 1
>
>will give you what you want, provided there are no spelling errors in the
name
>or address part.
>
>HTH
>
>--
>Oliver Willandsen - European Commission
>http://europa.eu.int
>All remarks are my own and do not necessarily
>reflect official European Commission policy
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Fri Sep 04 1998 - 08:01:04 CDT