On Thursday 21 February 2008 1:50 pm, Ed Leafe wrote:
> On Feb 21, 2008, at 3:41 PM, Uwe Grauer wrote:
> > Please try:
> > select entity_type, entity_fk, count(*) as ent_count from
> > assignment_rotation
> > group by entity_type, entity_fk
> > having count(*) > 1
>
> Of course, but that's not the question.
>
> The only reason for the 'having' clause is to be able to operate on
> the result set so that aggregate columns that don't exist in the
> sources can be filtered. The result set should have knowledge of
> 'ent_count' at that point (i.e., it works in the 'order by' clause).
>
> -- Ed Leafe
>
As to the why? An explanation from 12/11/2007 on the pg-general mailing list.
"Stanislav Raskin" <sr ( at ) brainswell ( dot ) de> writes:
> Still no Idea why it does not work with the alias.
SELECT-list output aliases name the *output* columns of the SELECT.
Those output columns are not available to the SELECT's computation
clauses. Otherwise you'd have a logical circularity --- by definition,
the output row(s) can't have been computed yet when you're evaluating
a WHERE condition.
People frequently think that because ORDER BY can refer to the
output-column aliases, other clauses should be able to do it too,
but this is not so. Conceptually, ORDER BY happens after computation
of the output rows, so there's no logical inconsistency in allowing
it to do that ... but it's certainly confused enough people that
one wonders whether the SQL committee shouldn't have left that wart
out of the language.
regards, tom lane
--
Adrian Klaver
[EMAIL PROTECTED]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
