Re: subquery question

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