Re: Problems with group by ... order by - Mailing list pgsql-general

"John D. Burger" <john@mitre.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