Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
Re: Problems with group by ... order by
- From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
- To: "John D. Burger" <john(at)mitre(dot)org>
- Cc: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
- Subject: Re: Problems with group by ... order by
- Date: Wed, 05 Oct 2005 15:11:11 -0400
- Message-id: <1632(dot)1128539471(at)sss(dot)pgh(dot)pa(dot)us>
"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
- References:
- Problems with group by ... order by
- From: John D. Burger
- Problems with group by ... order by
- Prev by Date: SPI_prepare, SPI_execute_plan do not return rows when using parameters
- Next by Date: Re: SPI_prepare, SPI_execute_plan do not return rows when using parameters
- Previous by thread: Re: Problems with group by ... order by
- Next by thread: SPI_prepare, SPI_execute_plan do not return rows when using parameters
- Indexes: