FAQ

SQL question....

Karl Denninger
May 20, 2008 at 6:41 pm
.... 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.

--
Karl Denninger (karl@denninger.net)
http://www.denninger.net
reply

Search Discussions

11 responses

  • Chester c young at May 20, 2008 at 7:06 pm

    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;
  • Karl Denninger at May 20, 2008 at 7:17 pm

    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
  • Harold A. Giménez Ch. at May 20, 2008 at 7:54 pm
    I think this is what you're looking for:

    SELECT * FROM access
    WHERE ip IN(SELECT ip FROM access
    GROUP BY ip HAVING count(*) > 1)
    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
  • Gurjeet Singh at May 20, 2008 at 8:58 pm

    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,
    --
    gurjeet[.singh]@EnterpriseDB.com
    singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

    EnterpriseDB http://www.enterprisedb.com

    Mail sent from my BlackLaptop device
  • Karl Denninger at May 20, 2008 at 11:17 pm

    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,
    --
    A small modification got CLOSE.... I can live with that set of
    results..... I think.

    Thanks :)
  • Gurjeet Singh at May 21, 2008 at 2:53 am

    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<mailto:
    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,



    --
    gurjeet[.singh]@EnterpriseDB.com
    singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

    EnterpriseDB http://www.enterprisedb.com

    Mail sent from my BlackLaptop device
  • Karl Denninger at May 21, 2008 at 3:10 am

    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,

    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)

    Karl Denninger (karl@denninger.net)
    http://www.denninger.net
  • Gurjeet Singh at May 21, 2008 at 7:52 am

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

    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<mailto:
    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,



    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.


    --
    gurjeet[.singh]@EnterpriseDB.com
    singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

    EnterpriseDB http://www.enterprisedb.com

    Mail sent from my BlackLaptop device
  • Karl Denninger at May 21, 2008 at 11:39 am

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

    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,

    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 (karl@denninger.net)
    http://www.denninger.net
  • Steve Midgley at May 22, 2008 at 12:49 am

    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>
    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...
    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
  • Karl Denninger at May 22, 2008 at 1:12 am

    Steve Midgley wrote:
    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>
    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...
    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
    I'm using Inet but the searches/replaces that need to be done are all
    done on the full address.

    In this case it doesn't do a bit of good because the entire table has to
    be sequential scanned.

    Karl Denninger (karl@denninger.net)
    http://www.denninger.net

Related Discussions