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

Referencing subselect returned value

P: n/a
I'd like to reference values returned by a subselect in the same SELECT
query.

Example:

SELECT id,
( SELECT COUNT(*) FROM second ) AS value_to_reference,
( value_to_reference + 1 ) AS value_that_uses_referenced_one
FROM first;

Any suggestions?

Thank you,
Marco
---------------------------(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 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Tue, Jan 20, 2004 at 21:21:56 +0100,
Marco Lazzeri <ma*******@noze.it> wrote:
I'd like to reference values returned by a subselect in the same SELECT
query.

Example:

SELECT id,
( SELECT COUNT(*) FROM second ) AS value_to_reference,
( value_to_reference + 1 ) AS value_that_uses_referenced_one
FROM first;

Any suggestions?


If you want to use a subselect repeatedly, you can either repeat it or
join it. Doing a join will normally be better.

Repeated select example:
SELECT id,
( SELECT COUNT(*) FROM second) AS value_to_reference,
( SELECT COUNT(*) FROM second)+1 AS value_to_reference_one,
FROM first;

Join example:
SELECT id,
cnt AS value_to_reference,
cnt+1 AS value_reference_one,
FROM first, (select COUNT(*) AS cnt from second) AS third;

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.