Re: Help with DISTINCT

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Mar 2007 14:06:04 -0800
Message-ID: <1173218763.588558@bubbleator.drizzle.com>

amerar_at_iwc.net wrote:

> On Mar 6, 1:04 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> ame..._at_iwc.net wrote:

>>> Hi All,
>>> I have this log-assed query. I need to make it such that I am getting
>>> a distinct EMAIL value for that column. I'm not sure how to do that
>>> with this query......it has no group by or anything...
>>> select
>>> cv.First_name,
>>> cv.Last_name,
>>> cv.email,
>>> p.name as Product,
>>> cp.Date_Entered as "Start Date",
>>> cp.exp_date as "Expiration Date",
>>> (cp.QUANTITY * p.base_price) as "Amount $"
>>> from customer_product cp, customer_visitor cv, product p, TEMP_ADID_1
>>> t
>>> where cp.customer_id = t.customer_id
>>> and cp.Date_Entered >= to_date('01.01.2000','mm/dd/rrrr')
>>> and cp.Date_Entered < to_date('02.02.2007','mm/dd/rrrr')
>>> and cp.customer_visitor_id = cv.customer_visitor_id
>>> and cp.PRODUST_ID = p.PRODUST_ID;
>>> Thanks in advance!!

>> My first thought is SELECT DISTINCT but it would seem from what little
>> you have provided that each person can have one, and only one, email
>> address so please define DISTINCT as you are using it.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> 
> 
> Well, DISTINCT in the moddile of this query will not work.   I am
> looking to get one and only one of each email address, regardless of
> the other values in the other columns......

Then redesign your application. That is not what it was built to do.

What you are indicating with your response is that a single customer_id with a single customer_vistor_id can point to multiple customers in your customer_visitor table. You will be fighting this nonsense for the rest of the application's life.

The only alternative is to use MAX or MIN to grab one, and only one, email address but that a poor substitute for a good design.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Mar 06 2007 - 16:06:04 CST