Re: Subselect Question

Hi,

On Tue, 2004-11-02 at 09:05, Alex P wrote:
> 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.

Hm. Here it works.

select 1 as foo,(select 2) as bar union select 5 as foo,(select 1) as
bar order by bar;

 foo | bar
-----+-----
   5 |   1
   1 |   2


Postgresql 7.4.2 in this case.
You can also use the whole query as a subselect, for example:

SELECT name, max_pop FROM
(SELECT name, (SELECT max(pop) FROM cities WHERE
cities.state=states.name) AS max_pop FROM states) as statepop;

if you want to filter with where clauses or whatever.

Regards
Tino