Skip site navigation (1) Skip section navigation (2)

Re: SQL question....

From: Harold A(dot) Giménez Ch(dot) <harold(dot)gimenez(at)gmail(dot)com>
To: "Karl Denninger" <karl(at)denninger(dot)net>
Cc: "chester c young" <chestercyoung(at)yahoo(dot)com>, "sql pgsql" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: SQL question....
Date: 2008-05-20 19:54:11
Message-ID: c807ef1a0805201254m26c765fdla975ccbc4eb13d76@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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 <karl(at)denninger(dot)net> 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
>

In response to

pgsql-sql by date

Next:From: Gurjeet SinghDate: 2008-05-20 20:57:59
Subject: Re: SQL question....
Previous:From: Karl DenningerDate: 2008-05-20 19:17:34
Subject: Re: SQL question....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group