RE: rewrite group by query

[Rohan Karanjawala]

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).

Re: rewrite group by query

[Boris Dali]

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).