
select id 
  from clients 
 where id = ( select id 
                from clients 
               where count(id) = 1 ) ;

The error I get is that you can't do the AGGREGATE int he WHERE clause,
but this is with a pre-v6.5 server too...technically, should the above be
possible?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Tue, 30 Mar 1999 13:18:17 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Should the following work...?
The Hermit Hacker wrote:
> 
> select id
>   from clients
>  where id = ( select id
>                 from clients
>                where count(id) = 1 ) ;
> 

What are you trying to do, grab the id 
of the first row in the table?

If this is so, try:

	select id from clients limit 1;

Otherwise, I can't figure out what
the above code is trying to accomplish.

Best,

Clark

Tue, 30 Mar 1999 18:34:40 +0000
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] Should the following work...?
The Hermit Hacker wrote:
>
> select id
>   from clients
>  where id = ( select id
>                 from clients
>                where count(id) = 1 ) ;
>

Hmm.  If you are trying to identify 
duplicate id's then try :

select distinct id from client x
where 1 < 
  ( select count(id) 
      from client y
     where y.id = x.id );


Ideally, this would be done as:

select a from 
   ( select a, count(a) cnt 
       from test 
   group by a ) where cnt < 2;

However, PostgreSQL dosn't support
dynamic views.  This, btw, is a 
very useful feature.

Hope this helps,

Clark

Tue, 30 Mar 1999 18:44:35 +0000
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] Should the following work...?