Re: Subqueries in select clause

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;