SQL question....

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.

many ways:

select a1.* from access a1 where exists(
select 1 from access a2 where a2.name=a2.name and a1.ip!=a2.ip );

select a1.*
from access a1
join access a2 using( name )
where a1.ip != a2.ip;

On Tue, May 20, 2008 at 3:17 PM, Karl Denninger wrote:

chester c young wrote:

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.




many ways:

select a1.* from access a1 where exists(
select 1 from access a2 where a2.name=a2.name and a1.ip!=a2.ip );

select a1.*
from access a1
join access a2 using( name )
where a1.ip != a2.ip;



Those will return single entries as well (which is easy to do with an
"ORDER BY", that is computationally simpler)

What I want (and can't figure out) is a SELECT that returns ONLY tuples
with two or more NAME entries that have the same IP.

-- Karl

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger 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.

Gurjeet Singh wrote:
On Tue, May 20, 2008 at 11:44 PM, Karl Denninger 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,
--

On Wed, May 21, 2008 at 4:47 AM, Karl Denninger wrote:

Gurjeet Singh wrote:
results..... I think.

Gurjeet Singh wrote:
On Wed, May 21, 2008 at 4:47 AM, Karl Denninger wrote:

Gurjeet Singh wrote:

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger
<karl@denninger.net 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,

On Wed, May 21, 2008 at 8:33 AM, Karl Denninger wrote:

Gurjeet Singh wrote:

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)

Gurjeet Singh wrote:
On Wed, May 21, 2008 at 8:33 AM, Karl Denninger wrote:

Gurjeet Singh wrote:
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

At 12:20 PM 5/21/2008, pgsql-sql-owner@postgresql.org wrote:
Date: Wed, 21 May 2008 06:39:11 -0500
From: Karl Denninger <karl@denninger.net>
To: Gurjeet Singh <singh.gurjeet@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL question....
Message-ID: <483409DF.7050905@denninger.net>
Its a very large table and is indexed already...

Steve Midgley wrote:
Not to completely beat this thing to death, but are you using an inet
or other custom datatype for this? I think if you index ip's using a
custom data type and search/group for specific octets, you'll get much
better performance than just searching via a regular b-tree string
index..

http://www.postgresql.org/docs/8.3/static/datatype-net-types.html

Steve