Re: SQL question: GROUP BY and MAX

Paul Tiseo wrote:
>
> In article <9eg8q3$np8$1_at_news.tue.nl>, hidders_at_REMOVE.THIS.win.tue.nl
> says...
> > SELECT a
> > FROM tabA
> > GROUP BY a
> > HAVING COUNT(*) = 5;
> >
> > This is equal to:
> >
> > SELECT DISTINCT a
> > FROM tabA r1
> > WHERE ( SELECT COUNT(*)
> > FROM tabA r2
> > WHERE r1.a = r2.a ) = 5;
>
> Would there/should there be performance differences, geenrally
> speaking? or, would most advanced db's SQL optimizers reduce it to the
> same operations? Thanks.

There is a significant difference between the two statements. When doing a group by, the DBMS has to sort all the rows that are not excluded by the conditions of the where clause. Only after this sorting has been done, the having clause can be evaluated.
On the other hand, when doing the select count(*) in the subselect in combination with a distinct, the condition is applied first and only the reduced result set has to be sorted.

Regards,
Heinz Received on Sun Jul 22 2001 - 01:26:07 CEST