Problems with group by ... order by

["John D. Burger" <john@xxxxxxxxx>]

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

Basically, I want to get an aggregate count of users across countries 
(including the "None" country), then I want to aggregate those 
countries with counts less than 300 into an Other pseudo-country.  I 
want it sorted by this final count, except I want the Other entry to be 
last.  This works fine if I leave out the first order-by condition - 
the result is sorted by the final count.  But with the query as written 
above, I get:

  ERROR:  column "country2" does not exist

It also works fine if I just order by country2, count2 - it seems to be 
the comparison that's the problem.  And ordering by a boolean after 
aggregating works fine in this simpler case:

  select country, count(*) as cnt
	from userProfiles
	group by country
	order by country is null, cnt;

This puts the NULL count at the end.

This is with PG 7.4.7.  Any advice appreciated, including how to do 
this in a simpler fashion.

- John Burger
  MITRE


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster