pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubqueries in select clause
6+ messages / 5 participants
[nested] [flat]
* Subqueries in select clause
@ 2001-04-18 09:45 Sara Cohen <sarina@cs.huji.ac.il>
2001-04-18 14:12 ` Re: Subqueries in select clause Tom Lane <tgl@sss.pgh.pa.us>
2001-04-18 14:17 ` Re: Subqueries in select clause Richard Huxton <dev@archonet.com>
2001-04-18 14:23 ` Re: Subqueries in select clause Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-04-18 15:44 ` Re: Subqueries in select clause Josh Berkus <josh@agliodbs.com>
0 siblings, 4 replies; 6+ messages in thread
From: Sara Cohen @ 2001-04-18 09:45 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am attempting to use subqueries in the select clause of a query
and am encountering difficulties.
The Problem:
------------
I would like to use a subquery that returns one column, but more than one
tuple. The result of this subquery is then used in an aggregate function.
For example, suppose I have a table c, with columns a and b of
numbers. Then I would like to be able to write something of the style:
select max((select count(b) from c group by a));
However, when I try it, I get an error message:
ERROR: More than one tuple returned by a subselect used as an expression.
I actually need to use this type of construct in many cases, some of which
have subqueries using values that appear in the outer query (i.e.,
correlated subqueries). Thus, it would be difficult for me to simply
create a temporary table with the value of the inner query and then use it
to solve the query I need. (Since I need to translate automatically from
queries with the above type of constructs to something that will run on
Postgresql.)
Using Oracle, I could get by this problem with:
select max(d) from ((select count(b) as d from c group by a));
However, my version of postgres doesn't support subqueries in the from
clause.
My Version:
-----------
sarina=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
(1 row)
Perhaps this has been solved in a later version?
Any suggestions on what to do will be greatly appreciated!
Sara
Department of Computer Science
The Hebrew University
Jerusalem, Israel
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Subqueries in select clause
2001-04-18 09:45 Subqueries in select clause Sara Cohen <sarina@cs.huji.ac.il>
@ 2001-04-18 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2001-04-18 14:12 UTC (permalink / raw)
To: Sara Cohen <sarina@cs.huji.ac.il>; +Cc: pgsql-sql
Sara Cohen <sarina@cs.huji.ac.il> writes:
> Using Oracle, I could get by this problem with:
> select max(d) from ((select count(b) as d from c group by a));
> However, my version of postgres doesn't support subqueries in the from
> clause.
Time to update to 7.1...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Subqueries in select clause
2001-04-18 09:45 Subqueries in select clause Sara Cohen <sarina@cs.huji.ac.il>
@ 2001-04-18 14:17 ` Richard Huxton <dev@archonet.com>
3 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2001-04-18 14:17 UTC (permalink / raw)
To: Sara Cohen <sarina@cs.huji.ac.il>; +Cc: pgsql-sql
Sara Cohen <sarina@cs.huji.ac.il> said:
> Hi,
>
> I am attempting to use subqueries in the select clause of a query
> and am encountering difficulties.
>
> The Problem:
> ------------
>
> I would like to use a subquery that returns one column, but more than one
> tuple. The result of this subquery is then used in an aggregate function.
> Using Oracle, I could get by this problem with:
>
> select max(d) from ((select count(b) as d from c group by a));
>
> However, my version of postgres doesn't support subqueries in the from
> clause.
In 7.1 at least you can do it if you alias the sub-query:
select max(d) from (select count(b) as d from c group by a) as calias;
Sorry, I don't know if 7.0.2 handles this (but you probably want to upgrade
anyway - I seem to remember some problems with 7.0.2)
HTH
- Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Subqueries in select clause
2001-04-18 09:45 Subqueries in select clause Sara Cohen <sarina@cs.huji.ac.il>
@ 2001-04-18 14:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
3 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2001-04-18 14:23 UTC (permalink / raw)
To: Sara Cohen <sarina@cs.huji.ac.il>; +Cc: pgsql-sql
On Wed, 18 Apr 2001, Sara Cohen wrote:
> The Problem:
> ------------
>
> I would like to use a subquery that returns one column, but more than one
> tuple. The result of this subquery is then used in an aggregate function.
>
> For example, suppose I have a table c, with columns a and b of
> numbers. Then I would like to be able to write something of the style:
>
> select max((select count(b) from c group by a));
>
> However, when I try it, I get an error message:
>
> ERROR: More than one tuple returned by a subselect used as an expression.
Yeah, technically I think the spec (at least my sql92 draft) guards
against this by saying that the set functions can't take set functions
or subqueries.
> I actually need to use this type of construct in many cases, some of which
> have subqueries using values that appear in the outer query (i.e.,
> correlated subqueries). Thus, it would be difficult for me to simply
> create a temporary table with the value of the inner query and then use it
> to solve the query I need. (Since I need to translate automatically from
> queries with the above type of constructs to something that will run on
> Postgresql.)
>
> Using Oracle, I could get by this problem with:
>
> select max(d) from ((select count(b) as d from c group by a));
Upgrade to 7.1 :)
Actually, technically for postgres it'll be:
select max(d) from (select count(b) as d from c group by a) e;
It enforces the requirement of naming the subqueries.
However in 7.0, you *might* be able to do something like:
select count(b) as d from c group by a order by 1 desc limit 1;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Subqueries in select clause
2001-04-18 09:45 Subqueries in select clause Sara Cohen <sarina@cs.huji.ac.il>
@ 2001-04-18 15:44 ` Josh Berkus <josh@agliodbs.com>
2001-04-18 18:33 ` Re: Subqueries in select clause Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2001-04-18 15:44 UTC (permalink / raw)
To: Sara Cohen <sarina@cs.huji.ac.il>; +Cc: pgsql-sql
Sara,
Hey! Great to see that Postgres has made it to Israel. What's the
most popular Linux distribution there?
I think you have your answer ... an upgrade. RPMs for most major
distributions of Linux should be available within the week.
Until you can upgrade, though, try this approach:
CREATE VIEW c_aggregate AS
SELECT sum(a) as sum_a, b FROM c GROUP BY b;
SELECT max(sum_a) FROM c_aggregate;
Not as fast or dynamic as a subselect, but should solve your immediate
problem.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Subqueries in select clause
2001-04-18 09:45 Subqueries in select clause Sara Cohen <sarina@cs.huji.ac.il>
2001-04-18 15:44 ` Re: Subqueries in select clause Josh Berkus <josh@agliodbs.com>
@ 2001-04-18 18:33 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2001-04-18 18:33 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Sara Cohen <sarina@cs.huji.ac.il>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> Until you can upgrade, though, try this approach:
> CREATE VIEW c_aggregate AS
> SELECT sum(a) as sum_a, b FROM c GROUP BY b;
> SELECT max(sum_a) FROM c_aggregate;
Unfortunately that won't work in 7.0 --- grouped views have a lot of
problems in that version, and one of the problems is that you can't
do another level of aggregating on their results.
Basically a view and a subselect are the same thing, so you can't get
around the restrictions of one by using the other...
7.1 is what Sara needs.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-04-18 18:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-18 09:45 Subqueries in select clause Sara Cohen <sarina@cs.huji.ac.il>
2001-04-18 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-18 14:17 ` Richard Huxton <dev@archonet.com>
2001-04-18 14:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-04-18 15:44 ` Josh Berkus <josh@agliodbs.com>
2001-04-18 18:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox