On 10/11/05 8:50 AM, "Rick Schumeyer" <[EMAIL PROTECTED]> wrote:
> The following query returns an error ("column c does not exist") in pg 8.0.3: > > > > (The column 'state' is the two letter abbreviation for a US state) > > > > -- get the number of rows for each state; list in descending order; include > only states with at least 6 rows > > select state, count(state) as c from t group by state having c > 5 order by c > desc; -- gives error > > > > If I leave the having clause out, I get the expected results: > > > > select state, count(state) as c from t group by state order by c desc; -- this > works > > > > Is this a bug or a feature? I'm not sure why I can use 'c' in the order by > clause but not the having clause. pg is much happier with the full "having > count(state) > 5". Will this cause count to be evaluated twice? I think that postgres is smart enough to do the evaluation only once, but this might be version-dependent, but one of the gurus will have to comment on which version (if there is a version dependence) first made this improvement. Sean ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend