Gurjeet Singh wrote:
On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
Gurjeet Singh wrote:
On Wed, May 21, 2008 at 4:47 AM, Karl Denninger
<[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote:
Gurjeet Singh wrote:
On Tue, May 20, 2008 at 11:44 PM, Karl Denninger
<[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>>
wrote:
.... assuming the following schema:
create table access (name text, address ip)
I want to construct a SELECT statement which will
return ONLY
tuples containing IP and name pairs IF there is an IP
that has two
or more NAMEs associated with it.
I've not figured out how to do this; I can get a list
of all IPs
and names ordered by IP, which I could then parse with
a different
program (e.g. "Select name, address from access order by
address"), but the idea of course is to do it with one
SELECT
statement and return only rows that have multiple
names listed for
a given IP.
try this:
select ip, name from access where ip in ( select ip from
access group by ip having count(name) > 2);
heven't execued it, so may need some coaxing. Let me know
the results.
Best regards,
--
A small modification got CLOSE.... I can live with that set
of results..... I think.
I am glad.
Harold had posted almost identical solution one hour before I did
(I had the mail ready to be sent almost after you posted, but
lost power and network connection for about an hour).
Can you please post your modified query, for the record; we might
still be able to get you _exactly_ what you want.
Best regards,
I used an "order by" and also increased the count to "> 2" because
there are a lot of blank "name" records in there as well (but I
don't want to select on those; as an artifact of how the system
works there will usually be a blank name entry for most IP
corresponding entries, but not all)
You can add a filter to the subquery using
WHERE name <> ''
Also, if you don't have it already, you may create an index on IP
column for better performance.
Mail sent from my BlackLaptop device
Its a very large table and is indexed already...
Karl Denninger ([EMAIL PROTECTED])
http://www.denninger.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
