pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Subqueries 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>
  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 14:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sara Cohen <sarina@cs.huji.ac.il>
  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 14:17  Richard Huxton <dev@archonet.com>
  parent: Sara Cohen <sarina@cs.huji.ac.il>
  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 14:23  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Sara Cohen <sarina@cs.huji.ac.il>
  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 15:44  Josh Berkus <josh@agliodbs.com>
  parent: Sara Cohen <sarina@cs.huji.ac.il>
  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 18:33  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  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