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?

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

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

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

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

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

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

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