Re: Having without group by clause

krishnanand_at_hotmail.com wrote:

> (1) Select I,II from X
> where exists
> ( select III from Y
> where X.I=Y.III
> having sum(IV) > 1 ) ;
>
> The Query works fine . One thing to NOTICE here is that, in the
> subquery the column being selected & the column being aggregated in the
> having clause are different !!!
>
> (2) I executed the queries :
>
> (a) select III from Y having sum(IV) > 1 ;
>
> (b) select III from Y where 1>2 having sum(IV) > 1 ; --- thinking that
>
> WHERE clause may be necessary. BUT BOTH FAIL.

Both the queries in (2) require a group by clause as III may have varying values for a stand-alone query. I believe that as the value of III is bound in the driving querying, the group by aggregation is implicit for the sub-query.I would advise against this approach however as you should find that;

Select I,II from X
 where 1 < ( select sum(iv) from Y

     where X.I=Y.III )

is more efficient (see for yourself using explain plan).

Regards,

Ian Received on Thu Nov 27 1997 - 00:00:00 CST