You can't use the alias name in the sort, case, where etc.. you have
to use the entire subselect.
So you would order by (select max(pop)...)
and you would also case the full thing as well.
A bit of a pain but Tom Lane explained it in a post a couple days ago
and said the system was optimized so it actually only ran the subquery
once.
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
________________________________________________________________________________
Hi,
when creating a query with a subselect
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) AS max_pop
FROM states;
then it is not possible to sort after max_pop or use max_pop in a function or a CASE.
am I dont anything wrong or is this meant to be the case?
Thanks
Alex
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly