RE: rewrite group by query
try this out select a, b from (select a from tab1 group by a having count(*)=1) alias where a in alias; regds, Rohan From: elain he [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: rewrite group by query Date: Mon, 05 Jan 2004 07:24:25 -0800 Hi, Does anyone have a better way of rewriting the following query? I'm trying to avoid querying the table, tab1 twice. select a, b from tab1 where a in (select a from tab1 group by a having count(*)=1); Thanks. elain _ Make your home warm and cozy this winter with tips from MSN House Home. http://special.msn.com/home/warmhome.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). _ Games, MMS cards, ringtones. Operator logos, picture messages more. http://server1.msn.co.in/sp03/mobilesms/ Jazz up your mobile! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rohan Karanjawala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
rewrite group by query
Hi, Does anyone have a better way of rewriting the following query? I'm trying to avoid querying the table, tab1 twice. select a, b from tab1 where a in (select a from tab1 group by a having count(*)=1); Thanks. elain _ Make your home warm and cozy this winter with tips from MSN House Home. http://special.msn.com/home/warmhome.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: rewrite group by query
How about select * from (select tab1.*, count(a) over(partition by a) a_count from tab1) where a_count =1; ... would probably save you one pass over tab1. Thanks, Boris Dali. --- elain he [EMAIL PROTECTED] wrote: Hi, Does anyone have a better way of rewriting the following query? I'm trying to avoid querying the table, tab1 twice. select a, b from tab1 where a in (select a from tab1 group by a having count(*)=1); Thanks. elain _ Make your home warm and cozy this winter with tips from MSN House Home. http://special.msn.com/home/warmhome.armx -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
