Re: Problems with group by ... order by

"John D. Burger" <john(at)mitre(dot)org> writes:
> I can't figure out why the following doesn't work:
> select
> (case
> when count1 < 300 then 'Other'
> else country1
> end) as country2,
> sum(count1) as count2
> from (select coalesce(country, 'None') as country1, count(*) as count1
> from userProfiles group by country1) as counts1
> group by country2
> order by (country2 = 'Other'), count2 desc

> ERROR: column "country2" does not exist

ORDER BY (and also GROUP BY) permit references to output column names
only when they are *unadorned*. You cannot use them in expressions.

This is a compromise between SQL92 and SQL99 rules ... it's a bit ugly.

regards, tom lane