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