Subselect Question

[Alex P]

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@postg resql.org so that your
message can get through to the mailing list cleanly

[Tino Wildenhain]

Re: Subselect Question

Hi,

On Tue, 2004-11-02 at 09:05, Alex P wrote:[color=blue]
> 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.[/color]

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=st ates.name) AS max_pop FROM states) as statepop;

if you want to filter with where clauses or whatever.

Regards
Tino


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tino Wildenhain]

Re: Subselect Question

Hi,

On Tue, 2004-11-02 at 09:05, Alex P wrote:[color=blue]
> 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.[/color]

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=st ates.name) AS max_pop FROM states) as statepop;

if you want to filter with where clauses or whatever.

Regards
Tino


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Sim Zacks]

Re: Subselect Question

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@postg resql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Sim Zacks]

Re: Subselect Question

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@postg resql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Richard Huxton]

Re: Subselect Question

Alex P wrote:[color=blue]
> 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.[/color]

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_sta te
;

--
Richard Huxton
Archonet Ltd

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

[Richard Huxton]

Re: Subselect Question

Alex P wrote:[color=blue]
> 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.[/color]

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_sta te
;

--
Richard Huxton
Archonet Ltd

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