From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Sara Cohen <sarina(at)cs(dot)huji(dot)ac(dot)il> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Subqueries in select clause |
Date: | 2001-04-18 14:23:30 |
Message-ID: | Pine.BSF.4.21.0104180712120.81945-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Roberto Mello | 2001-04-18 14:54:24 | Re: maybe Offtopic : PostgreSQL & PHP ? |
Previous Message | Richard Huxton | 2001-04-18 14:17:38 | Re: Subqueries in select clause |