Thread: Subqueries in select clause
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
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
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;
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
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"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