Thread: Should the following work...?

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 

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

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