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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
>
>

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