From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "John D(dot) Burger" <john(at)mitre(dot)org> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problems with group by ... order by |
Date: | 2005-10-05 19:11:11 |
Message-ID: | 1632.1128539471@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-10-05 19:41:17 | Re: SPI_prepare, SPI_execute_plan do not return rows when using parameters |
Previous Message | Dennis Jenkins | 2005-10-05 19:10:54 | SPI_prepare, SPI_execute_plan do not return rows when using parameters |