Обсуждение: Subselect Question

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

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

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

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.

Here max_pop is naming the whole subselect. How about something like:

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

--
   Richard Huxton
   Archonet Ltd