RE: [HACKERS] Should the following work...?

Just talked to one of our Oracle guru's here at hte office, and he had to
shake his head a bit :)

To find duplicate records, or, at least, data in a particular field, he
suggests just doing:

   SELECT id,count(1)
     FROM clients
 GROUP BY id
   HAVING count(1) > 1;

A nice, clean, simple solution :)

On Tue, 30 Mar 1999, The Hermit Hacker wrote:

> 
> Using:
> 
> select id
>   from clients
>  where id = ( select id
>                 from clients
>                group by id
>                having count(id) = 1 ) ;
> 
> 
> I get:
> 
> ERROR:  rewrite: aggregate column of view must be at rigth side in qual
> 
> 
> 
> On Tue, 30 Mar 1999, Jackson, DeJuan wrote:
> 
> > > 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?
> > I believe instead of WHERE that should be a HAVING clause.
> > But I'm not sure PostgreSQL can handle a HAVING in a sub-select.
> > 
> > 	-DEJ
> > 
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org 
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 
> 
> 

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