Re: Select DISTINCT question

> "Seefelt, Beth" wrote:
>
> Hi everyone,
>
> Is one of these more correct than the other ?
>
> 1) select distinct customer_number, address_code from orders;
>
> 2) select customer_number, address_code
> from orders
> group by customer_number,address_code;
>
> Our developers do the 1st one mostly. It always bothers me, I'm not
> sure why, I guess I just think of it as looking ambiguous even though
> it does return the same result as the second option.
>
> Any opinions? Does it matter at all?
>
> TIA,
>
> Beth

I think that it doesn't make much of a difference. You'll have to scan ORDERS and sort, unless you have a concatenated index on both columns, in which case it should be reasonably fast. It's very difficult to say 'write this' or 'write that'. It depends on many factors, the indexes you have, the relative volumes (few customers and many orders ? Then I'd rather try a scan of the customer table and AND EXISTS() to check for orders. Many customers few orders? Then you are better off directly processing ORDERS). Try to think of as many different ways to do it as you can, then try on your data. I am no great fan of DISTINCT, I tend to interpret it as 'fetching from the wrong table' (when there is one table) or 'lousy programming with missing conditions' when there are several tables.

-- 
HTH,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).