By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Subselect Question

P: n/a
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #2

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #3

P: n/a
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 ma*******@postgresql.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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

P: n/a
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 ma*******@postgresql.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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5

P: n/a
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

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

Nov 23 '05 #6

P: n/a
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

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

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.