Horton
Menu

Thread

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

    Jackson, DeJuan <djackson@cpsgroup.com> — 1999-03-30T17:42:21Z

    > 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

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

    Marc Fournier <scrappy@hub.org> — 1999-03-30T18:27:20Z

    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

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

    Marc Fournier <scrappy@hub.org> — 1999-03-30T18:44:34Z

    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

  4. Re: [HACKERS] Should the following work...?

    Clark C . Evans <clark.evans@manhattanproject.com> — 1999-03-30T18:47:58Z

    The Hermit Hacker wrote:
> 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 :)

Ya.  That's pretty.  For some
reason I always forget using the
'HAVING' clause, and end up using
a double where clause.  

:) Clark

  5. Re: [HACKERS] Should the following work...?

    Marc Fournier <scrappy@hub.org> — 1999-03-30T19:08:33Z

    Ya, that's what I forgot too :(  Its not something I use everyday, so
never think about it :)


On Tue, 30 Mar 1999, Clark Evans wrote:

> The Hermit Hacker wrote:
> > 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 :)
> 
> Ya.  That's pretty.  For some
> reason I always forget using the
> 'HAVING' clause, and end up using
> a double where clause.  
> 
> :) Clark
> 

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