Обсуждение: HAVING ...

Is there a reason (other then it hasn't been implemented yet?) that the 
following couldn't work?
  SELECT id,count(id) AS cnt    FROM table   WHERE id IN ( 1,2,3,4,5)
GROUP BY id  HAVING cnt = 2;

instead of:
  SELECT id,count(id) AS cnt    FROM table   WHERE id IN ( 1,2,3,4,5)
GROUP BY id  HAVING count(id) = 2;

The second one would have to 're-run' the COUNT against the table, would 
it not?  Whereas the first would take the existing results?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Is there a reason (other then it hasn't been implemented yet?) that the 
> following couldn't work?

>    SELECT id,count(id) AS cnt
>      FROM table
>     WHERE id IN ( 1,2,3,4,5)
> GROUP BY id
>    HAVING cnt = 2;

It's contrary to the SQL spec, for one thing ...

>    SELECT id,count(id) AS cnt
>      FROM table
>     WHERE id IN ( 1,2,3,4,5)
> GROUP BY id
>    HAVING count(id) = 2;

> The second one would have to 're-run' the COUNT against the table, would 
> it not?

No, it doesn't.  We've optimized out duplicate aggregate calls for
awhile now.
        regards, tom lane

On Wed, 20 Apr 2005, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> Is there a reason (other then it hasn't been implemented yet?) that the
>> following couldn't work?
>
>>    SELECT id,count(id) AS cnt
>>      FROM table
>>     WHERE id IN ( 1,2,3,4,5)
>> GROUP BY id
>>    HAVING cnt = 2;
>
> It's contrary to the SQL spec, for one thing ...

Of course it is *sigh*

Thanks ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664