Re: Select DISTINCT question
IMHO since they both require Oracle to sort the table and you are doing a full table scan in both cases the performance would be virtually the same. Rick Seefelt, Beth Beth.Seefelt@tetlTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] eyusa.comcc: Sent by: Subject: Select DISTINCT question [EMAIL PROTECTED] 02/06/2002 09:53 AM Please respond to ORACLE-L 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Select DISTINCT question
Beth, Without going into very much detail about the two selects. 1 will return only the records that are distinct from each other and 2 will return all rows in a particular order. Using 1 could result in the elimination of records if there is more than one record that meets the distinct criteria. Order by will show duplicate records easily. ROR mª¿ªm [EMAIL PROTECTED] 02/06/02 09:53AM 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 maReceived: from CONNECT-MTA by galottertter at all? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Select DISTINCT question
The second one does not make sense. Group by is normally used when you are including a function that would return a single value (like sum or count) and yet want to have more than one row returned because you are including a column that is not within the function so 1 is definitely more correct --- Seefelt, Beth [EMAIL PROTECTED] 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 __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Select DISTINCT question
Ron, What am I missing? Both selects will ALWAYS return the same data. Correct??? Rick Ron Rogers RROGERS@galoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ttery.org cc: Sent by: Subject: Re: Select DISTINCT question root@fatcity. com 02/06/2002 11:13 AM Please respond to ORACLE-L Beth, Without going into very much detail about the two selects. 1 will return only the records that are distinct from each other and 2 will return all rows in a particular order. Using 1 could result in the elimination of records if there is more than one record that meets the distinct criteria. Order by will show duplicate records easily. ROR mª¿ªm [EMAIL PROTECTED] 02/06/02 09:53AM 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 maReceived: from CONNECT-MTA by galottertter at all? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Select DISTINCT question
I think Ron is talking about ORDER BY instead of GROUP BY ... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 06, 2002 11:08 AM To: Multiple recipients of list ORACLE-L Subject: Re: Select DISTINCT question Ron, What am I missing? Both selects will ALWAYS return the same data. Correct??? Rick Ron Rogers RROGERS@galoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ttery.org cc: Sent by: Subject: Re: Select DISTINCT question root@fatcity. com 02/06/2002 11:13 AM Please respond to ORACLE-L Beth, Without going into very much detail about the two selects. 1 will return only the records that are distinct from each other and 2 will return all rows in a particular order. Using 1 could result in the elimination of records if there is more than one record that meets the distinct criteria. Order by will show duplicate records easily. ROR mª¿ªm [EMAIL PROTECTED] 02/06/02 09:53AM 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 maReceived: from CONNECT-MTA by galottertter at all? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling
Re: Select DISTINCT question
-- Please see the official ORACLE-L FAQ: htt p://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Select DISTINCT question
I think he just misread the group by as an order by -Original Message- Sent: Wednesday, February 06, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Ron, What am I missing? Both selects will ALWAYS return the same data. Correct??? Rick Ron Rogers RROGERS@galoTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ttery.org cc: Sent by: Subject: Re: Select DISTINCT question root@fatcity. com 02/06/2002 11:13 AM Please respond to ORACLE-L Beth, Without going into very much detail about the two selects. 1 will return only the records that are distinct from each other and 2 will return all rows in a particular order. Using 1 could result in the elimination of records if there is more than one record that meets the distinct criteria. Order by will show duplicate records easily. ROR mª¿ªm [EMAIL PROTECTED] 02/06/02 09:53AM 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 maReceived: from CONNECT-MTA by galottertter at all? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: Select DISTINCT question
Beth is correct in her statement. I'm sorry if some of my responses to this question are getting cut off but the network people are working on upgrading the email service. listed is my response to tom that was clipped by the email server. --- Rick, You are correct. Both statements will return the same results. It could be the wrong results depending on the circumstances. Duplicate records will not show up. An ORDER BY is needed for that in this situation. Distinct and group by remove the duplicates.. Ron [EMAIL PROTECTED] 02/06/02 01:28PM I think he just misread the group by as an order by trailer infor removed--- Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
