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

Reuse of Subselects

P: n/a
Hi all,

if I have something like this:

SELECT column1,
(... complicated subselect ...),
column1 - (... same subselect as above ...)
FROM table1;

do I really have to rewrite the subselect a 2nd time if I need that
result in another column's expression?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
Depend in the exact query, you can do:

SELECT column1, x.c, column1-x.c
FROM table1, (... complicated subselect ...) as x;

The above may not work if they're correlated, so you can try:

SELECT column1, column2, column1-column2
FROM
(SELECT column1, (... complicated subselect ...) as column2
FROM table1);

Hope this helps,

On Tue, Feb 17, 2004 at 01:02:43PM +0100, Holger Marzen wrote:
Hi all,

if I have something like this:

SELECT column1,
(... complicated subselect ...),
column1 - (... same subselect as above ...)
FROM table1;

do I really have to rewrite the subselect a 2nd time if I need that
result in another column's expression?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAMgd6Y5Twig3Ge+YRAuTFAKDKWtaonB+zOZIO+415Eo uziuPptACgq7is
xBSqRe0p0KpTz8/MrHnx8zQ=
=93WW
-----END PGP SIGNATURE-----

Nov 22 '05 #2

P: n/a
temporary tables work. Save the complicated
subselect in temporary table, following
queries just simple select on temp table.

Holger Marzen wrote:
Hi all,

if I have something like this:

SELECT column1,
(... complicated subselect ...),
column1 - (... same subselect as above ...)
FROM table1;

do I really have to rewrite the subselect a 2nd time if I need that
result in another column's expression?


--
P. J. "Josh" Rovero Sonalysts, Inc.
Email: ro****@sonalysts.com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
************************************************** *********************

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #3

P: n/a
On Tue, Feb 17, 2004 at 13:02:43 +0100,
Holger Marzen <ho****@marzen.de> wrote:
Hi all,

if I have something like this:

SELECT column1,
(... complicated subselect ...),
column1 - (... same subselect as above ...)
FROM table1;

do I really have to rewrite the subselect a 2nd time if I need that
result in another column's expression?


You should be able to use a join. The syntax won't be simpler, but you
might get nearly a 2 fold speed up.

Assuming that the subselect depends on the current row, you want to do
something like:

SELECT (
SELECT column1, complicated, column1 - complicated FROM
(complicated select AS complicated) AS comptable)
) FROM table1;

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.