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

Re: SQL question....

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "Karl Denninger" <karl(at)denninger(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SQL question....
Date: 2008-05-20 20:57:59
Message-ID: 65937bea0805201357q50071460sa1a72074c64c7c70@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <karl(at)denninger(dot)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,
-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

In response to

Responses

pgsql-sql by date

Next:From: Karl DenningerDate: 2008-05-20 23:17:19
Subject: Re: SQL question....
Previous:From: Harold A. Giménez Ch.Date: 2008-05-20 19:54:11
Subject: Re: SQL question....

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