From: | Tino Wildenhain <tino(at)wildenhain(dot)de> |
---|---|
To: | Alex P <alex(at)meerkatsoft(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Subselect Question |
Date: | 2004-11-02 08:36:34 |
Message-ID: | 1099384594.21062.514.camel@sabrina.peacock.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2004-11-02 08:48:54 | Re: Postgres Versions / Releases |
Previous Message | M.A. Oude Kotte | 2004-11-02 08:23:44 | Re: Numeric type problems |