Skip site navigation (1) Skip section navigation (2)

Re: subquery question

From: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
To: Tom Raney <twraney(at)comcast(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: subquery question
Date: 2009-02-01 03:58:56
Message-ID: 396486430901311958v69eef0e1h2e1792d3c509123f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Sat, Jan 31, 2009 at 4:13 PM, Tom Raney <twraney(at)comcast(dot)net> wrote:

> I've dug around on the Postgres lists for a bit and I haven't found a good
> explanation of why this query is not allowed.

> WHERE Temp.count_agents = (SELECT MIN (Temp.count_agents) FROM Temp)

At this point, the sub-select on Temp wont work because Temp does
exist at its level of scope.  It don't fully understand how to use 8.4
new CTE feature, but I might allow something like this to work.  Using
SQL-92, you can make this work using two level of aggregation and
joining the result back to the original table.

SELECT Temp.team_id, Temp.count_agents
  FROM (SELECT TR.team_id, COUNT(TR.agent_id) AS count_agents
          FROM teamrel TR
      GROUP BY TR.team_id ) AS Temp
 WHERE Temp.count_agents = (SELECT MIN( teamcount )
                              FROM ( SELECT COUNT(*) AS teamcount
                                       FROM Teamrel
                                   GROUP BY T1.team_id ));


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

In response to

pgsql-novice by date

Next:From: Ruzsinszky AttilaDate: 2009-02-01 11:04:32
Subject: LATIN2->UTF8 conversation with dblink
Previous:From: Tom RaneyDate: 2009-02-01 00:13:15
Subject: subquery question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group