| Lists: | pgsql-hackers |
|---|
| From: | "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> |
|---|---|
| To: | The Hermit Hacker <scrappy(at)hub(dot)org>, pgsql-hackers(at)postgreSQL(dot)org |
| Subject: | RE: [HACKERS] Should the following work...? |
| Date: | 1999-03-30 17:42:21 |
| Message-ID: | D05EF808F2DFD211AE4A00105AA1B5D2037A50@cpsmail |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
> 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
| From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
|---|---|
| To: | "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> |
| Cc: | pgsql-hackers(at)postgreSQL(dot)org |
| Subject: | RE: [HACKERS] Should the following work...? |
| Date: | 1999-03-30 18:27:20 |
| Message-ID: | Pine.BSF.4.05.9903301425400.55565-100000@thelab.hub.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
|---|---|
| To: | "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | RE: [HACKERS] Should the following work...? |
| Date: | 1999-03-30 18:44:34 |
| Message-ID: | Pine.BSF.4.05.9903301442590.55565-100000@thelab.hub.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | Clark Evans <clark(dot)evans(at)manhattanproject(dot)com> |
|---|---|
| To: | The Hermit Hacker <scrappy(at)hub(dot)org>, pgsql-hackers(at)postgreSQL(dot)org |
| Subject: | Re: [HACKERS] Should the following work...? |
| Date: | 1999-03-30 18:47:58 |
| Message-ID: | 37011C5E.459FC923@manhattanproject.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
|---|---|
| To: | Clark Evans <clark(dot)evans(at)manhattanproject(dot)com> |
| Cc: | pgsql-hackers(at)postgreSQL(dot)org |
| Subject: | Re: [HACKERS] Should the following work...? |
| Date: | 1999-03-30 19:08:33 |
| Message-ID: | Pine.BSF.4.05.9903301508120.55565-100000@thelab.hub.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org