Quantcast

SQL question....

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQL question....

Karl Denninger
.... 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 ([hidden email])
http://www.denninger.net





--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

chester c young
> 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;




     

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Karl Denninger
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Harold A. Giménez Ch.
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Gurjeet Singh-3
In reply to this post by Karl Denninger
On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Karl Denninger
Gurjeet Singh wrote:

> On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[hidden email]
> <mailto:[hidden email]>> 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 :)




--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Gurjeet Singh-3
On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[hidden email]> wrote:
Gurjeet Singh wrote:

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[hidden email] <mailto:[hidden email]>> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Karl Denninger
Gurjeet Singh wrote:
On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[hidden email]> wrote:
Gurjeet Singh wrote:

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[hidden email] <mailto:[hidden email]>> 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 ([hidden email])
http://www.denninger.net

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Gurjeet Singh-3
On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <[hidden email]> wrote:
Gurjeet Singh wrote:
On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[hidden email]> wrote:
Gurjeet Singh wrote:

On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[hidden email] <mailto:[hidden email]>> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Karl Denninger

Gurjeet Singh wrote:

> On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Gurjeet Singh wrote:
>>     On Wed, May 21, 2008 at 4:47 AM, Karl Denninger
>>     <[hidden email] <mailto:[hidden email]>> wrote:
>>
>>         Gurjeet Singh wrote:
>>
>>             On Tue, May 20, 2008 at 11:44 PM, Karl Denninger
>>             <[hidden email] <mailto:[hidden email]>
>>             <mailto:[hidden email] <mailto:[hidden email]>>>
>>             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 ([hidden email])
http://www.denninger.net





--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Steve Midgley
In reply to this post by Karl Denninger
At 12:20 PM 5/21/2008, [hidden email] wrote:

>Date: Wed, 21 May 2008 06:39:11 -0500
>From: Karl Denninger <[hidden email]>
>To: Gurjeet Singh <[hidden email]>
>Cc: [hidden email]
>Subject: Re: SQL question....
>Message-ID: <[hidden email]>
>
> > 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


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQL question....

Karl Denninger

Steve Midgley wrote:

> At 12:20 PM 5/21/2008, [hidden email] wrote:
>> Date: Wed, 21 May 2008 06:39:11 -0500
>> From: Karl Denninger <[hidden email]>
>> To: Gurjeet Singh <[hidden email]>
>> Cc: [hidden email]
>> Subject: Re: SQL question....
>> Message-ID: <[hidden email]>
>>
>> > 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 ([hidden email])
http://www.denninger.net





--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Loading...